Re: How to return more than one row of data from a - Mailing list pgsql-sql

I have a bit trouble following the problem, but I think it may be worth mentioning, that you can return multiple rows
bydefining the function return as
 

CREATE FUNCTION foo_func(int,int) RETURNS SETOF <tablename> AS 'SELECT * FROM <tablename> 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" <drew@xyzzy.dhs.org> 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