CODEWITHSUNDEEP
postgresqlplpgsql
Alan Wayne
Alan Wayne

Reputation: 5384

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

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:

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

Upvotes: 2

Views: 7419

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45835

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;

Upvotes: 4

Related Questions