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

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

From
dinocherian@yahoo.com (dino ck)
Date:
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


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

From
"Andrew G. Hammond"
Date:
-----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
ALIASFOR $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-----


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

From
Dino Cherian
Date:
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


Re: How to return more than one row of data from a

From
"Johnny J\xF8rgensen"
Date:
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





Re: How to return more than one row of data from a

From
MindTerm
Date:
Dear all,
 Can you create a user defined type by using CREATE
TYPE ?

M.T.


--- "Johnny_J�rgensen" <pgsql@halfahead.dk> wrote:
> 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
> 
> 
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1


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

From
"Andrew G. Hammond"
Date:
-----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-----


How to create function with unspecified number of input parameters ?

From
MindTerm
Date:
Dear all,
 I am writing to ask how to create function ( CREATE
FUNCTION ) with unspecified number of input parameters
? e.g. I like to create a function similar to the
structure of COALESCE :

COALESCE( ... , [..] )

M.T.



__________________________________________________
Do You Yahoo!?
Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
http://geocities.yahoo.com/ps/info1


Re: How to create function with unspecified number of input parameters ?

From
Tom Lane
Date:
MindTerm <mindterm@yahoo.com> writes:
>   I am writing to ask how to create function ( CREATE
> FUNCTION ) with unspecified number of input parameters

CREATE FUNCTION doesn't presently support that.

If the underlying function is written in C, you can fake it by
issuing multiple CREATE FUNCTION commands with different numbers
of declared parameters, all of which point to the same C function.
Then the C code would have to look at fcinfo->nargs to see how it
had been called.
        regards, tom lane


Re: How to create function with unspecified number of input parameters

From
Bruce Momjian
Date:
> MindTerm <mindterm@yahoo.com> writes:
> >   I am writing to ask how to create function ( CREATE
> > FUNCTION ) with unspecified number of input parameters
> 
> CREATE FUNCTION doesn't presently support that.
> 
> If the underlying function is written in C, you can fake it by
> issuing multiple CREATE FUNCTION commands with different numbers
> of declared parameters, all of which point to the same C function.
> Then the C code would have to look at fcinfo->nargs to see how it
> had been called.

Is this a TODO item?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: How to create function with unspecified number of input parameters ?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is this a TODO item?

Dunno.  I certainly wouldn't be in favor of unspecified arguments
(like ANSI C's "..." construct), because it'd totally destroy our
datatype resolution mechanism.  Optional arguments might work, but
it needs some careful thought.
        regards, tom lane


Re: How to create function with unspecified number of input

From
"Aasmund Midttun Godal"
Date:
Perhaps you could pass an array into it instead?

Regards,

Aasmund.

On Sat, 24 Nov 2001 20:25:21 -0800 (PST), MindTerm <mindterm@yahoo.com> wrote:
> Dear all,
> 
>   I am writing to ask how to create function ( CREATE
> FUNCTION ) with unspecified number of input parameters
> ? e.g. I like to create a function similar to the
> structure of COALESCE :
> 
> COALESCE( ... , [..] )
> 
> M.T.
> 
> 
> 
> __________________________________________________
> 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 4: Don't 'kill -9' the postmaster

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46