Select from a table variable
plpgsql, postgresql, postgresql-9.2
Solution
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 visible in the same session, but not outside.
One alternative would be to use cursors in PL/pgSQL.
More alternatives:
- SELECT multiple rows and columns into a record variable
Problem
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.