从表变量中选择 [英] 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).

一种替代方法是在plpgsql中使用 cursors ，

One alternative would be to use cursors in plpgsql, like described in the manual here.

这篇关于从表变量中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！