postgresql 如何在 plpgsql 中使用记录类型变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34019898/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use a record type variable in plpgsql?
提问by SG. Nihonbashi
How can I use query result stored into a record type variable for another query within the same stored function? I use Postgres 9.4.4.
如何将存储在记录类型变量中的查询结果用于同一存储函数中的另一个查询？我使用 Postgres 9.4.4。
With a table like this:
有这样一张桌子：
create table test (id int, tags text[]);
insert into test values (1,'{a,b,c}'),
(2,'{c,d,e}');
I wrote a function (simplified) like below:
我写了一个函数（简化）如下：
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
$$;
... but didn't work:
...但没有用：
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
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
回答by Erwin Brandstetter
The core misunderstanding: a recordvariable holds a singlerow (or is NULL), not a table (0-n rows of a well-known type). There are no "table variables" in Postgres or PL/pgSQL. Depending on the task, there are various alternatives:
核心误解：record变量保存单行（或为 NULL），而不是表（已知类型的 0-n 行）。Postgres 或 PL/pgSQL中没有“表变量”。根据任务的不同，有多种选择：
Accordingly, you cannot assign multiplerows to a recordtype variable. In this statement:
因此，您不能将多行分配给一个record类型变量。在此声明中：
EXECUTE format('SELECT id, tags FROM %s', _tbl) INTO t;
... Postgres assigns only the first rowand discards the rest. Since "the first" is not well defined in your query, you end up with an arbitrary pick. Obviously due to the misunderstanding mentioned at the outset.
... Postgres仅分配第一行并丢弃其余行。由于“第一个”在您的查询中没有明确定义，因此您最终会得到任意选择。显然是因为开头提到的误解。
A recordvariable also cannot be used in place of tables in SQL queries. That's the primary cause of the error you get:
一个record变量也可以不到位SQL查询表的使用。这是您得到错误的主要原因：
relation "t" does not exist
关系“t”不存在
It should be clear by now, that count(*)wouldn't make any sense to begin with, since tis just a single record / row - besides being impossible anyway.
现在应该很清楚了，这count(*)从一开始就没有任何意义，因为t它只是一个记录/行 - 除了无论如何都是不可能的。
Finally (even if the rest would work), your return type seems wrong: (t TEXT[], e TEXT[])id, tagsinto t, you'd want to return something like (id int, e TEXT[]).
最后（即使其余的都可以），您的返回类型似乎是错误的：(t TEXT[], e TEXT[])id, tags进入t，您希望返回类似(id int, e TEXT[]).
What you are trying to do 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;
Call (note the syntax!):
调用（注意语法！）：
SELECT * FROM func('test');
Related:
有关的：
Just a proof of concept. While you are selecting the whole table, you would just use the underlying table instead. In reality you'll have some WHEREclause in the query ...
只是一个概念证明。当您选择整个表时，您只需使用基础表即可。实际上，您将WHERE在查询中包含一些子句...
Careful of the lurking type mismatch, count()returns bigint, you couldn't assign that to an integervariable. Would need a cast: count(*)::int.
小心潜伏的类型不匹配，count()返回bigint，您不能将其分配给integer变量。需要演员阵容：count(*)::int.
But I replaced that completely, it's cheaper to run this right after EXECUTE:
但是我完全替换了它，在以下之后运行它更便宜EXECUTE：
GET DIAGNOSTICS _ct = ROW_COUNT;
Why ANALYZE?
为什么ANALYZE？
Aside: CTEs in plain SQL can often do the job:
旁白：普通 SQL 中的 CTE 通常可以完成这项工作：
