Received: from xyzzy.lan.internal (d150-146-168.home.cgocable.net [24.150.146.168]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAN7TVm05919 for ; Fri, 23 Nov 2001 02:29:31 -0500 (EST) (envelope-from drew@xyzzy.dhs.org) Received: from localhost ([127.0.0.1] helo=there ident=drew) by xyzzy.lan.internal with smtp (Exim 3.32 #1 (Debian)) id 167Am8-0003kV-00; Fri, 23 Nov 2001 02:29:28 -0500 Content-Type: text/plain; charset="iso-8859-1" From: "Andrew G. Hammond" To: dinocherian@yahoo.com (dino ck), pgsql-sql@postgresql.org Subject: Re: How to return more than one row of data from a function in PL/pgSQL Date: Fri, 23 Nov 2001 02:29:25 -0500 X-Mailer: KMail [version 1.3.2] References: In-Reply-To: MIME-Version: 1.0 Message-Id: Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id fAN7WUn06025 X-Archive-Number: 200111/332 X-Sequence-Number: 5714 -----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@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-----