pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How 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