postgresql 从表变量中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17873735/
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
Select from a table variable
提问by Xin
I am trying to save the result of a SELECTquery, pass it, and reuse it in another PL/pgSQL function:
我试图保存SELECT查询的结果，传递它，并在另一个 PL/pgSQL 函数中重用它：
DECLARE
table_holder my_table; --the type of table_holder is my_table;
result text;
BEGIN
SELECT * INTO table_holder FROM table_holder ;
result = another_function(table_holder);
return result;
END
The code for another_function(table_holder my_table), respectively:
的代码another_function(table_holder my_table)分别为：
BEGIN
RETURN QUERY
SELECT col FROM table_holder where id = 1;
END
Is it possible to run a SELECTquery on a variable? If not, is there a way to get around this limitation?
是否可以对SELECT变量运行查询？如果没有，有没有办法绕过这个限制？
I am using PostgreSQL 9.2.
我正在使用 PostgreSQL 9.2。
回答by Erwin Brandstetter
There are no "table variables" in plpgsql. That's something you would find in SQL Server.
plpgsql 中没有“表变量”。这是您可以在 SQL Server 中找到的东西。
Use a temporary tableinstead:
改用临时表：
BEGIN
CREATE TEMP TABLE table_holder AS
SELECT * FROM table_holder
WHERE <some condition>
ORDER BY <some expression>
;
...
END
A temporary table exists for the lifetime of a session. To drop it at the end of the function (or an enclosing transaction) automatically use ON COMMIT DROPin the creation statement.
临时表存在于会话的生命周期内。要在函数（或封闭事务）的末尾删除它，请自动ON COMMIT DROP在创建语句中使用。
CREATE TEMP TABLE table_holder ON COMMIT DROP AS
SELECT ...
The temporary table is automatically visible to any other function in the same session (or transaction respectively).
临时表对同一会话（或分别为事务）中的任何其他函数自动可见。
One alternative would be to use cursorsin plpgsql, like described in the manual here.
一种替代方法是在 plpgsql 中使用游标，如手册中所述。
