从表变量中选择 [英] Select from a table variable
问题描述
我试图保存SELECT
查询的结果,将其传递并在另一个PL/pgSQL函数中重用:
I am trying to save the result of a SELECT
query, pass it, and reuse it in another PL/pgSQL function:
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
分别为another_function(table_holder my_table)
的代码:
BEGIN
RETURN QUERY
SELECT col FROM table_holder where id = 1;
END
是否可以对变量运行SELECT
查询?如果没有,是否有办法解决此限制?
Is it possible to run a SELECT
query on a variable? If not, is there a way to get around this limitation?
我正在使用PostgreSQL 9.2.
I am using PostgreSQL 9.2.
推荐答案
plpgsql中没有表变量".那是您在SQL Server中会发现的东西.
There are no "table variables" in plpgsql. That's something you would find in SQL Server.
使用 临时表 代替:
Use a temporary table instead:
BEGIN
CREATE TEMP TABLE table_holder AS
SELECT * FROM table_holder
WHERE <some condition>
ORDER BY <some expression>
;
...
END
在会话的生存期内存在一个临时表.要在函数(或封闭的事务)末尾自动删除它,请在创建语句中使用ON COMMIT DROP
.
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 DROP
in the creation statement.
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 cursors in plpgsql, like described in the manual here.
这篇关于从表变量中选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!