Postgresql 如何在 plpgsql 中使用记录类型变量

发布于12月02日

如何将存储在记录类型变量中的查询结果用于同一存储函数中的另一个查询？我使用Postgres 9.4.4.

有这样一张桌子:

create table test (id int, tags text[]);
insert into test values (1,'{a,b,c}'),
                        (2,'{c,d,e}');

我编写了一个函数(简化)，如下所示:

CREATE OR REPLACE FUNCTION func(_tbl regclass)
RETURNS TABLE (t TEXT[], e TEXT[])
LANGUAGE plpgsql AS $$
DECLARE
  t RECORD;
  c INT;
BEGIN
  EXECUTE format('SELECT id, tags FROM %s', _tbl) INTO t;
  SELECT count(*) FROM t INTO c;
  RAISE NOTICE '% results', c;
  SELECT * FROM t;
END
$$;

... 但没有奏效:

select func('test');

ERROR:  42P01: relation "t" does not exist
LINE 1: SELECT count(*) FROM t
                             ^
QUERY:  SELECT count(*) FROM t
CONTEXT:  PL/pgSQL function func(regclass) line 7 at SQL statement
LOCATION:  parserOpenTable, parse_relation.c:986

推荐答案

核心误解是:record变量包含single行(或NULL)，而不是表(0-n行的已知类型).有no "table variables" in Postgres or PL/pgSQL个.根据任务的不同，有多种 Select :

因此，不能将multiple行分配给record类型变量.在本声明中:

EXECUTE format('SELECT id, tags FROM %s', _tbl) INTO t;

... 博士后分配only the first row名学生，其余学生放弃.因为"第一个"在查询中没有很好的定义，所以最终会出现任意 Select .显然是因为一开始提到的误解.

在SQL查询中，record变量也不能用来代替表.这是导致错误的主要原因:

关系"t"不存在

现在应该很清楚，count(*)一开始就没有任何意义，因为t只是一个记录/行——而且无论如何都是不可能的.

最后(即使剩下的也行)，您的返回类型似乎是错误的:~~(t TEXT[], e TEXT[])~~.既然你在t中 Select 了id, tags，你会想要返回(id int, e TEXT[])这样的值.

你想做什么would work like this:

CREATE OR REPLACE FUNCTION func(_tbl regclass)
  RETURNS TABLE (id int, e text[]) AS
$func$
DECLARE
   _ct int;
BEGIN
   EXECUTE format(
      'CREATE TEMP TABLE tmp ON COMMIT DROP AS
       SELECT id, tags FROM %s'
    , _tbl);

   GET DIAGNOSTICS _ct = ROW_COUNT; -- cheaper than another count(*)

   -- ANALYZE tmp;  -- if you are going to run multiple queries

   RAISE NOTICE '% results', _ct;

   RETURN QUERY TABLE tmp;
END
$func$ LANGUAGE plpgsql;

拨打(note the syntax!):