Received: from tina.andersenit.dk (tina.andersenit.dk [193.89.137.127] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id fANCnWm37781 for ; Fri, 23 Nov 2001 07:49:33 -0500 (EST) (envelope-from pgsql@halfahead.dk) Received: from ([130.226.84.71]) by tina.andersenit.dk (Merak 4.00.40) with SMTP id BHB36607; Fri, 23 Nov 2001 13:49:33 +0100 Message-ID: <200111231349040322.00DB44C8@mail.halfahead.dk> In-Reply-To: <20011123122043.66620.qmail@web21006.mail.yahoo.com> References: <20011123122043.66620.qmail@web21006.mail.yahoo.com> X-Mailer: Calypso Version 3.30.00.00 (4) Date: Fri, 23 Nov 2001 13:49:04 +0100 From: =?us-ascii?Q?=22Johnny_J=F8rgensen=22?= To: "Dino Cherian" Cc: pgsql-sql@postgresql.org Subject: Re: How to return more than one row of data from a Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id fAND2Vn38202 X-Archive-Number: 200111/339 X-Sequence-Number: 5721 I have a bit trouble following the problem, but I think it may be worth mentioning, that you can return multiple rows by defining the function return as CREATE FUNCTION foo_func(int,int) RETURNS SETOF AS ' SELECT * FROM WHERE intval BETWEEN $1 AND $2; ' LANGUAGE 'sql'; - hope it helps.. *********** REPLY SEPARATOR *********** On 23-11-2001 at 04:20 Dino Cherian wrote: >Hi, > >Thanks Andrew G. Hammond, but it has some problem, I think and >suspect. > >It seems working, but can it be used in a multi-user environment. I >think there will be problem with identification of which all data >belongs to whom. > >Regards >Dino > >--- "Andrew G. Hammond" wrote: >> -----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----- >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 2: you can get off all lists at once with the unregister >> command >> (send "unregister YourEmailAddressHere" to >majordomo@postgresql.org) > > >__________________________________________________ >Do You Yahoo!? >Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. >http://geocities.yahoo.com/ps/info1 > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly