CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsql
Putra L Zendrato
Putra L Zendrato

Reputation: 332

how to return multiple row from multiple table in plpgsql?

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

Upvotes: 1

Views: 2063

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45940

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)

Upvotes: 4

Related Questions