plpgpsql - differents forms of temporary table in order to do a sort

Question

I'm trying to optimize a function that has 7 WITH temporary tables, acting as sort mechanisms, cascading from the initial temporary table/sort to the next one until the last one e.g the 7th temporary table/ sort.

Gist here: This kind of code must be forbidden.

I'm trying to replace the WITH sort with a real temporary table e.g CREATE TEMPORARY TABLE <table_name> AS SELECT col1 FROM another_table;. The aim is to increase performance as the query in its current form is very very slow.

This is the change I've proposed

CREATE OR REPLACE FUNCTION report.get_sa001(
  IN "date_D" timestamp without time zone,
  IN "date_F" timestamp without time zone,
  IN frequence integer)
RETURNS TABLE(
  "Period_date" timestamp without time zone, 
  "Site" character varying, 
  "Customer_code" character varying, 
  "Internal_reference" character varying, 
  "InvoiceNumber" character varying, 
  "Value_in_currency" numeric, 
  "Value_in_EUR" numeric, 
  "Value_Budget_in_EUR" numeric, 
  "Selling_price_CUR" numeric, 
  "Selling_price_EUR" numeric, 
  "Currency_code" character varying, 
  "Selling_quantity" numeric, 
  "Variance_price_CUR" numeric, 
  "Variance_price_EUR" numeric, 
  "Variance_value_CUR" numeric, 
  "Variance_value_EUR" numeric, 
  "Selling_date" timestamp without time zone) AS

$BODY$
DECLARE
  p_debut timestamp without time zone;
DECLARE
  p_fin timestamp without time zone;
BEGIN
  p_debut = dw.get_period_end("date_D", "frequence");
  p_fin = dw.get_period_end("date_F", "frequence");

  RETURN QUERY 
    CREATE TEMPORARY TABLE "dates_1" AS
      SELECT
        p_debut::date + n AS "date",
        dw.period_frequency(p_debut::date + n) AS "frequency"               
      FROM generate_series(0, p_fin::date - p_debut::date) AS x(n)
      WHERE (dw.period_frequency(p_debut::date + n) & frequence != 0);

  SELECT * FROM "dates_1"; -- Thanks to Vao Tsun
END;
$BODY$
  LANGUAGE plpgsql STABLE
  COST 100
  ROWS 1000;

The creation of the function is fine but when running the function this way

SELECT * FROM report.get_sa001('2017-01-01'::date, '2017-01-31'::date, 32)

This is what I have

 ERROR: cannot open query CREATE TABLE AS like cursor
 État SQL :42P11
 Contexte : fonction PL/pgsql report.get_sa001(timestamp without time  zone,timestamp without time zone,integer), ligne 11 à RETURN QUERY

I tried to replace the CREATE TEMPORARY TABLE with a SELECT * INTO TEMPORARY TABLE. The creation is ok again but I have the same error when running it.

Checking SO's archive, it sounds PLPGSQL forbids using temporary table (check here).

If you have any ideas, they are more than welcomed.

Thanks

Answer 1

there is nothing wrong with using create temporary table in function:

t=# create or replace function so37() returns table (i int) as
$$
declare
begin
create temporary table a as select 2;
return query select * from a;
end;
$$ language plpgsql
;
CREATE FUNCTION
t=# select * from so37();
 i
---
 2
(1 row)

but they have to be maintained, eg in your in current example you lack drop table if exists, or instead of create table you should insert into, because if you don't, second run will fail:

t=# select * from so37();
ERROR:  relation "a" already exists
CONTEXT:  SQL statement "create temporary table a as select 2"
PL/pgSQL function so37() line 4 at SQL statement

And I believe CTE is a better alternative to creating temporary table in functions...