How to declare and use a temporary table as a cursor source in PostgreSQL?

Question

Having normalized (mostly 2NF and some 3NF tables), I now need a query like below to climb up and down my table structure to get the information I need. (PostgreSQL 9.3)

CREATE TEMPORARY TABLE test  AS
With charts_on_date_of_service AS ( 
    SELECT t.service_recid, t.tservice, t.chart_recid           
    FROM  ( select s.recid as service_recid, s.tservice, p.chart_recid
        from doctorservices d                   
        join services s on (s.recid = d.service_recid)      
        join patients p on (p.recid = s.patient_recid)      
        where s.tservice::date = _tservice::date        
        ) as t 
)
select s.recid as service_recid, s.tservice, c.chart_recid, c.tservice as time_of_service
from charts_on_date_of_service  c                   
join patients p on (p.chart_recid = c.chart_recid)          
join services s on (s.patient_recid = p.recid)              
join doctorservices d  on ( d.service_recid = s.recid)          
where s.tservice::date <= _tservice::date               
order by c.chart_recid, s.tservice;

As involved as this query is, I would like not to have to duplicate it within a plpgsql function. That is, I would like to do something like:

CREATE OR REPLACE FUNCTION test(_tservice timestamp)            
  RETURNS TABLE (service_recid bigint, chart_recid int, tservice timestamp, ct int) AS
$func$ 

DECLARE 

CREATE TEMPORARY TABLE test AS ....   <--THIS FAILS

cur CURSOR FOR
    SELECT t.service_recid, t.tservice, t.chart_recid   
    FROM   test t

BEGIN
      ... some processing commands on the temp table test and cursor cur....
END

I have several related questions:

How is a temporary table "Declared" in plpgsql?
Can a temporary table be used as a source for a cursor?
Would performance be better creating a single temporary table and using it in multiple places, or better to recreate the identical query in multiple places?

I can't seem to find the answers with Google. Any help or ideas is most appreciated.

Pavel Stehule · Accepted Answer · 2016-01-08 06:10:58Z

You have to use different approach. Inside PL/pgSQL any CREATE statement cannot be in DECLARE part. It is statement as any other and should be in function body part. If you can iterate on dynamically created table, you have to use unbound cursor, and you have to specify query in OPEN statement (or better - use FOR cycle):

CREATE OR REPLACE FUNCTION test(a int)
RETURNS TABLE (b int, c int, d int) AS $$
BEGIN
  DROP TABLE IF EXISTS foo; 
  CREATE TEMP TABLE foo(col int);
  INSERT INTO foo SELECT generate_series(1,a);
  FOR b, c, d IN SELECT col, col + 1, col + 2 FROM foo
  LOOP
    RETURN NEXT;
  END LOOP;
END; $$ LANGUAGE plpgsql;

This example works, but it is significantly expensive and this pattern should be used only when it is necessary. Temp tables are expensive, and if you don't need it, don't use it (there are good reasons: performance, complexity, but usually it isn't necessary). Some patterns from T-SQL are not be used in Postgres, and some work needs different thinking. You can use a arrays, RETURN NEXT, RETURN QUERY statements:

CREATE OR REPLACE FUNCTION test(a int)
RETURNS table (b int, c int, d int) AS $$
BEGIN
  RETURN QUERY SELECT col, col+1, col+2
                  FROM generate_series(1,a)
  RETURN;
END; $$ LANGUAGE plpgsql;

For similar trivial functions is better to use SQL language:

CREATE OR REPLACE FUNCTION test(a int)
RETURNS table (b int, c int, d int) AS $$
  SELECT col, col+1, col+2
     FROM generate_series(1,a)
$$ LANGUAGE sql;

You can use arrays in Postgres:

CREATE OR REPLACE FUNCTION test(a int)
RETURNS TABLE (b int, c int, d int) AS $$
DECLARE cols int[];
BEGIN
  cols := ARRAY(SELECT generate_series(1,a));
  RETURN QUERY 
    SELECT col, col + 1, col + 2 
       FROM unnest(cols) g(col);
  RETURN;
END; $$ LANGUAGE plpgsql;

Excellent answer. So if the temp table is very expensive, in my case, is it better to repeat the query; once for each of the two cursors I will need? Thanks.
@AlanWayne it depends how expensive is your query. The cost of temporary tables increases with frequency - 1/s in db (+/- zero), 100/s in db (pretty expensive). Always depends on context, but the behave is strongly different (and internal implementation is different too) than T-SQL. If it is necessary, use temp tables, but prefer a arrays (variables).

Collectives™ on Stack Overflow

How to declare and use a temporary table as a cursor source in PostgreSQL?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related