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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 2001 November 23 07:20 am, 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.

This isn't a problem since multi_results.r_id is generated from a SEQUENCE 
(multi_marker) and is thus unique to the result set from each call on the 
function. It is possible, and even likely to have data from more than one 
user in the multi_results table.  However, as long as you query it with WHERE 
clause that tests r_id = the result of multi_return (as in the example 
below), you will not get the other person's data.

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

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

iEYEARECAAYFAjv/boYACgkQCT73CrRXhLE+JgCfaL7ZhWFKDQOVRdTZBDvWJVgZ
fjMAniPyu5ABM1BG6WjW/yY1X/VC4AwT
=1YOr
-----END PGP SIGNATURE-----