postgresql - PostgreSQL 中如何声明和使用临时表作为游标源？

Question

规范化后(主要是 2NF 和一些 3NF 表)，我现在需要一个如下所示的查询来爬上爬下我的表结构以获取我需要的信息。 (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;                                    

与此查询一样，我希望不必在 plpgsql 函数中复制它。也就是说，我想做类似的事情:

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

我有几个相关的问题:

• 如何在 plpgsql 中“声明”临时表？
• 能否将临时表用作游标的源？
• 创建单个临时表并在多个地方使用它会更好，还是在多个地方重新创建相同的查询会更好？

我似乎无法通过 Google 找到答案。非常感谢任何帮助或想法。

Answer 1

你必须使用不同的方法。在 PL/pgSQL 中，任何 CREATE 语句都不能在 DECLARE 部分中。它和其他任何语句一样，应该在函数体部分。如果您可以迭代动态创建的表，则必须使用 unbound游标，并且您必须在 OPEN 语句中指定查询(或更好 - 使用 FOR 循环):

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;

这个例子可行，但是它非常昂贵并且应该只在必要时使用这个模式。临时表很昂贵，如果您不需要它，请不要使用它(有充分的理由:性能、复杂性，但通常没有必要)。 T-SQL 中的一些模式在 Postgres 中没有使用，有些工作需要不同的思考。您可以使用数组、RETURN NEXT、RETURN QUERY 语句:

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;

对于类似的琐碎功能，最好使用 SQL 语言:

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;

您可以在 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;