From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)? |
Date: | 2008-10-13 09:34:03 |
Message-ID: | 20081013093403.GE17873@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2008-10-13 09:35:33 | Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)? |
Previous Message | Grzegorz Jaśkiewicz | 2008-10-13 09:19:16 | Re: Drupal and PostgreSQL - performance issues? |