Select from a table variable

Question

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.

You already have thevalue in the varaible. What else do you want to select ? — Safeer, Jul 26 '13 at 05:42

Erwin Brandstetter · Accepted Answer · 2022-04-11T19:05:12.100

29

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

edited Apr 11 '22 at 19:05

answered Jul 26 '13 at 05:55

Erwin Brandstetter

605,456
145
1,078
1,228

A key difference between using temp tables and table variables is that a temp table remains visible until the *transaction* commits or it's dropped. Multiple or re-entrant calls to the function can have surprising and unexpected results because they see the same table, not isolated per-function table variables. I think temp tables are also a lot less efficient than tablevars. Whenever possible avoid using temp tables in stored procs. You can often do what you need to by passing refcursors or by having one function `SELECT` from another directly. – Craig Ringer Jul 26 '13 at 07:35
Temp tables live until the end of the *session* (not just transaction) by default. – Erwin Brandstetter Jul 25 '14 at 20:40
Yes, unless `ON COMMIT DROP`, as you've used above. I should've mentioned that explicitly. – Craig Ringer Jul 25 '14 at 23:39

Select from a table variable

1 Answers1

Linked