Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?

am Mon, dem 13.10.2008, um 12:17:21 +0300 mailte Vladimir Dzhuvinov folgendes:
>
> However, after consulting the docs and running a few tests, it looks
> like Postgresql misses a crucial feature which my application depends
> upon - returning multiple SELECT result sets from functions/stored
> procedures.
>
> So, is it true that as of Postgresql 8.3 there is no way to have a
> pgpqsql function return multiple SELECTs?

You can write so called SRF (Set Returning Function), read more about
this here:
http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS

Simple example:

test=# create or replace function srf (OUT a int, OUT b int) returns setof record as $$begin a:=1;b:=1;return next;a:=2;b:=3; return next; end;$$language plpgsql;
CREATE FUNCTION
test=*# select * from srf();
a | b
---+---
1 | 1
2 | 3
(2 rows)

or, simpler in plain sql:

test=# create or replace function srf (OUT a int, OUT b int) returns setof record as $$select 1,2;select 1,3;$$language sql;
CREATE FUNCTION
test=*#
test=*#
test=*# select * from srf();
a | b
---+---
1 | 3
(1 row)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net