Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
PL/SQL => Oracle, PgPL/SQL => PostgreSQL. Which one is it you're working with? –  Mat Mar 1 '13 at 5:40
    
@Mat: It's PL/pgSQL, btw. –  Erwin Brandstetter Aug 5 '13 at 12:09
add comment

1 Answer 1

up vote 5 down vote accepted

In PostgreSQL, every table name serves as type name automatically. So you can just declare a variable of that type in PL/pgSQL.

CREATE FUNCTION foo()
 RETURNS void LANGUAGE plpgsql AS
$func$
DECLARE
  q1 foo;
  q2 bar;
BEGIN

FOR q1 IN 
   SELECT * from foo
LOOP
   FOR q2 IN 
      SELECT * from bar
   LOOP
       -- do something with q1 and q2
       -- since q1 and q2 are well known types, you can access columns
       -- with attribute notation. Like: q1.col1
   END LOOP;
END LOOP;

END
$func$

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

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

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, though. Looping is a measure of last resort, when you can do things in one scan where you would need multiple scans in pure SQL.

share|improve this answer
1  
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 $$... –  Chris Travers Mar 1 '13 at 6:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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