Select from a table variable
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
The code for another_function(table_holder my_table)
, respectively:
BEGIN
RETURN QUERY
SELECT col FROM table_holder where id = 1;
END
Is it possible to run a SELECT
query on a variable? If not, is there a way to get around this limitation?
I am using PostgreSQL 9.2.
Solution 1:
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
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 creating 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.
One alternative would be to use cursors in plpgsql.