how to return multiple row from multiple table in plpgsql?

Question

how to return multiple row from multiple table in plpgsql, I tried to return rows from 2 tables with inner join? this my code

 CREATE OR REPLACE FUNCTION library.getallbookwithcategory()
 RETURNS SETOF library.book AS
  $BODY$
 DECLARE
 r library.book%rowtype;
 BEGIN
 FOR r IN select book.*,category.name from library.book left join library.category on category.id=book.category_id 
WHERE book.id > 0 order by dateadded ASC
LOOP
    -- can do some processing here
    RETURN NEXT r; 
END LOOP;
RETURN;
  END
  $BODY$
   LANGUAGE plpgsql

I need return name from category

Depending on what `some processing here` is exactly, you might get that even simpler/faster with a single `RETURN QUERY SELECT ...`. [This related answer](http://stackoverflow.com/questions/8605174/postgresql-error-42601-a-column-definition-list-is-required-for-functions-ret/8611675#8611675) may be of help. — Erwin Brandstetter, Feb 25 '14 at 22:50

score 4 · Accepted Answer · answered Feb 25 '14 at 12:32

There are more ways how to do it. One:

postgres=# SELECT * FROM boo;
 id | foo_id |  a  |  b   
----+--------+-----+------
  1 |      1 | 100 | 1000
  2 |      1 | 200 | 2000
  3 |      2 | 300 | 4000
(3 rows)

postgres=# SELECT * FROM foo;
 id | a | b 
----+---+---
  1 | 1 | 2
  2 | 3 | 4
(2 rows)

CREATE OR REPLACE FUNCTION foo_boo() 
RETURNS TABLE (foo_a int, foo_b int, boo_a int, boo_b int) AS $$
BEGIN            
  FOR foo_a, foo_b, boo_a, boo_b IN 
     SELECT f.a fa, f.b fb, b.a ba, b.b bb
        FROM foo f 
             JOIN boo b
             ON f.id = boo.foo_id
  LOOP
    RETURN NEXT;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT * FROM foo_boo();
 foo_a | foo_b | boo_a | boo_b 
-------+-------+-------+-------
     1 |     2 |   100 |  1000
     1 |     2 |   200 |  2000
     3 |     4 |   300 |  4000
(3 rows)

how to return multiple row from multiple table in plpgsql?

1 Answers1