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