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.

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 automatically visible to any other function in the same session.

One alternative would be to use cursors in plpgsql.

Comments

[Craig Ringer]

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.

[Erwin Brandstetter]

Temp tables live until the end of the session (not just transaction) by default.

[Craig Ringer]

Yes, unless ON COMMIT DROP, as you've used above. I should've mentioned that explicitly.