Re: How to return more than one row of data from a function in PL/pgSQL

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 2001 November 19 04:22 am, dino ck wrote:
> Hi,
>
> Is there a way to return more than one row of data from a function in
> PL/pgSQL?
>
> Anybody please help me with an example or a good resource on the net.

I don't know exactly what you're trying to achieve, BUT, you might want to try
a temporary table or even something like this...

- -- initialize
DROP SEQUENCE multi_marker; DROP TABLE multi_results, foo; DROP FUNCTION multi_return(text);
- -- create
CREATE TABLE foo(data TEXT NOT NULL);
CREATE SEQUENCE multi_marker;
CREATE TABLE multi_results(r_id INTEGER NOT NULL, data TEXT NOT NULL);
CREATE FUNCTION multi_return(text) RETURNS INTEGER AS ' DECLARE r_idx INTEGER;   ins_1 CONSTANT TEXT := ''INSERT INTO
multi_results(r_id, data) SELECT '';   ins_2 CONSTANT TEXT:= '', data || ''''_add'''' FROM foo WHERE '';   where_clause
ALIASFOR $1;   ins_final TEXT; BEGIN  r_idx := nextval(''multi_marker'');   ins_final := ins_1 || r_idx || ins_2 ||
where_clause;  RAISE NOTICE ''executing: %'', ins_final;   EXECUTE ins_final;   RETURN r_idx; END;' LANGUAGE
'plpgsql';
- -- populate
INSERT INTO foo VALUES (''); INSERT INTO foo VALUES ('a'); INSERT INTO foo VALUES ('b');
- -- usage
BEGIN;
SELECT multi_return('length(data) > 0'::text);   -- returns an index, ie 1
SELECT data FROM multi_results WHERE r_id = 1;         -- get results
DELETE FROM multi_results WHERE r_id = 1;             -- cleanup.
COMMIT;

- -- 
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org   http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F                  613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAjv9+tUACgkQCT73CrRXhLHGDACeMgpWfE8O1fHOkO7kFuNLNDvd
7XoAn10pv/9enQ9NyetvUp5s32iP3uO8
=57Z4
-----END PGP SIGNATURE-----