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 ALIAS FOR $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(at)xyzzy(dot)dhs(dot)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-----