I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like:

q1 = select * from foo;
q2 = select * from bar;
for t1 in q1:
  for t2 in q2:
    -- do something with t1 and t2

I looked into the return next construct but that seems to only able to handle return values.

  • 2
    PL/SQL => Oracle, PgPL/SQL => PostgreSQL. Which one is it you're working with?
    – Mat
    Mar 1, 2013 at 5:40
  • @Mat: It's PL/pgSQL, btw. Aug 5, 2013 at 12:09

1 Answer 1


In PostgreSQL, every table name serves as type name for the row type (a.k.a. composite type) automatically - not as table type, there are no "table types" or "table variables" in Postgres (but there are typed tables).
So you can declare a variable of that type in PL/pgSQL.

  RETURNS void
  LANGUAGE plpgsql AS
   q1 foo;  -- "foo" ...
   q2 bar;  -- ... and "bar" are existing (visible) table names
   FOR q1 IN 
      SELECT * FROM foo
      FOR q2 IN 
         SELECT * FROM bar
         -- do something with q1 and q2
         -- you can access columns with attribute notation like: q1.col1
      END LOOP;

You could also just declare variables of the generic type record. It takes any row type at assignment automatically. But special rules apply. Be sure to follow the link and read the chapter of the manual!

A FOR loop works with a built-in cursor. There are also explicit cursors in PL/pgSQL.

While it's often convenient to have the function return SETOF <table name>, returning SETOF record is not as convenient. The system does not know what the function returns this way and you have to add a column definition list with every call. Which is a pain. Details about table functions in the manual.

Often, there are more efficient solutions with plain SQL. Looping is a measure of last resort, when you can do things in one scan where you would need multiple scans in pure SQL.

  • 4
    Also note that if you want to return the table type you can just say something like CREATE FUNCTION footest() RETURNS SETOF foo LANGUAGE PLPGSQL AS $$... Mar 1, 2013 at 6:23
  • Why then we at all have the feature of "composite types" if we can create tables instead, without restrictions applied to composite types (no constraints, etc)? The only meaningful way of using composite types I see is returning a couple (or several) parameters from a function instead of creating a whole table for that. Feb 5, 2020 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.