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.

Answer 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 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