-----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-----