pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to return more than one row of data from a function in PL/pgSQL
5+ messages / 4 participants
[nested] [flat]
* How to return more than one row of data from a function in PL/pgSQL
@ 2001-11-19 09:22 dino ck <dinocherian@yahoo.com>
2001-11-23 07:29 ` Re: How to return more than one row of data from a function in PL/pgSQL Andrew G. Hammond <drew@xyzzy.dhs.org>
0 siblings, 1 reply; 5+ messages in thread
From: dinocherian@yahoo.com @ 2001-11-19 09:22 UTC (permalink / raw)
To: pgsql-sql
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.
Thanks
-- Dino
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to return more than one row of data from a function in PL/pgSQL
2001-11-19 09:22 How to return more than one row of data from a function in PL/pgSQL dino ck <dinocherian@yahoo.com>
@ 2001-11-23 07:29 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
2001-11-23 12:20 ` Re: How to return more than one row of data from a function in PL/pgSQL Dino Cherian <inimss@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Andrew G. Hammond @ 2001-11-23 07:29 UTC (permalink / raw)
To: dinocherian@yahoo.com; pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to return more than one row of data from a function in PL/pgSQL
2001-11-19 09:22 How to return more than one row of data from a function in PL/pgSQL dino ck <dinocherian@yahoo.com>
2001-11-23 07:29 ` Re: How to return more than one row of data from a function in PL/pgSQL Andrew G. Hammond <drew@xyzzy.dhs.org>
@ 2001-11-23 12:20 ` Dino Cherian <inimss@yahoo.com>
2001-11-23 12:49 ` Re: How to return more than one row of data from a "Johnny J�rgensen" <pgsql@halfahead.dk>
2001-11-24 09:55 ` Re: How to return more than one row of data from a function in PL/pgSQL Andrew G. Hammond <drew@xyzzy.dhs.org>
0 siblings, 2 replies; 5+ messages in thread
From: Dino Cherian @ 2001-11-23 12:20 UTC (permalink / raw)
To: Andrew G. Hammond <drew@xyzzy.dhs.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to return more than one row of data from a
2001-11-19 09:22 How to return more than one row of data from a function in PL/pgSQL dino ck <dinocherian@yahoo.com>
2001-11-23 07:29 ` Re: How to return more than one row of data from a function in PL/pgSQL Andrew G. Hammond <drew@xyzzy.dhs.org>
2001-11-23 12:20 ` Re: How to return more than one row of data from a function in PL/pgSQL Dino Cherian <inimss@yahoo.com>
@ 2001-11-23 12:49 ` "Johnny J�rgensen" <pgsql@halfahead.dk>
1 sibling, 0 replies; 5+ messages in thread
From: "Johnny Jørgensen" @ 2001-11-23 12:49 UTC (permalink / raw)
To: Dino Cherian <inimss@yahoo.com>; +Cc: pgsql-sql
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 <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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to return more than one row of data from a function in PL/pgSQL
2001-11-19 09:22 How to return more than one row of data from a function in PL/pgSQL dino ck <dinocherian@yahoo.com>
2001-11-23 07:29 ` Re: How to return more than one row of data from a function in PL/pgSQL Andrew G. Hammond <drew@xyzzy.dhs.org>
2001-11-23 12:20 ` Re: How to return more than one row of data from a function in PL/pgSQL Dino Cherian <inimss@yahoo.com>
@ 2001-11-24 09:55 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
1 sibling, 0 replies; 5+ messages in thread
From: Andrew G. Hammond @ 2001-11-24 09:55 UTC (permalink / raw)
To: Dino Cherian <inimss@yahoo.com>; pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-11-24 09:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-19 09:22 How to return more than one row of data from a function in PL/pgSQL dino ck <dinocherian@yahoo.com>
2001-11-23 07:29 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
2001-11-23 12:20 ` Dino Cherian <inimss@yahoo.com>
2001-11-23 12:49 ` Re: How to return more than one row of data from a "Johnny J�rgensen" <pgsql@halfahead.dk>
2001-11-24 09:55 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox