Re: How to return more than one row of data from a function in PL/pgSQL

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