Thread: function with multiple return values
I've created the following function:
CREATE OR REPLACE FUNCTION latest ( lot_id int4,
condition text, OUT perc smallint, OUT entry_date date )
RETURNS SETOF record AS
'
BEGIN
RETURN QUERY SELECT t1.perc, t1.entry_date
FROM t1, t2
WHERE t1.condition_id=t2.id and t1.lot_id = $1 and t2.code = $2
ORDER BY entry_date DESC LIMIT 1;
END;
' language 'plpgsql' VOLATILE;
It works for this:
select (latest(38787,'IP')).*
returning perc and entry_date each in it's own column.
Problem is:
select (latest(38787,'IP')).*, (latest(38787,'FI')).*;
returns 4 columns: perc, entry_date, perc, entry_date
Tried:
select perc as p1, perc as perc2 from (
select (latest(38787,'IP')).*, (latest(38787,'FI')).*
) as foo;
just to see -- it says perc is ambiguous... well yes it is! :)
Ideas on how to uniquely name the first and second set of "perc,
entry_date"?
Or maybe there is a different way to return 2 values from a function?
Thanks,
Scott
Scott Serr <serrs@theserrs.net> wrote: > I've created the following function: > > CREATE OR REPLACE FUNCTION latest ( lot_id int4, > condition text, OUT perc smallint, OUT entry_date date ) > RETURNS SETOF record AS > ' > BEGIN > RETURN QUERY SELECT t1.perc, t1.entry_date > FROM t1, t2 > WHERE t1.condition_id=t2.id and t1.lot_id = $1 and t2.code = $2 > ORDER BY entry_date DESC LIMIT 1; > END; > ' language 'plpgsql' VOLATILE; > > It works for this: > select (latest(38787,'IP')).* > returning perc and entry_date each in it's own column. > > Problem is: > select (latest(38787,'IP')).*, (latest(38787,'FI')).*; > returns 4 columns: perc, entry_date, perc, entry_date > > Tried: > select perc as p1, perc as perc2 from ( > select (latest(38787,'IP')).*, (latest(38787,'FI')).* > ) as foo; > just to see -- it says perc is ambiguous... well yes it is! :) > > Ideas on how to uniquely name the first and second set of "perc, > entry_date"? > Or maybe there is a different way to return 2 values from a function? You can use alias-names for the 2 queries, like: test=*# select * from foo(); a | b ---+--- 1 | 2 (1 row) Time: 0.279 ms test=*# select foobar.a as x, foobar.b as y, bar.* from (select * from foo()) foobar, (select * from foo()) bar ; x | y | a | b ---+---+---+--- 1 | 2 | 1 | 2 (1 row) Now you have unique column names. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Andreas Kretschmer <akretschmer@spamfence.net> writes:
> Scott Serr <serrs@theserrs.net> wrote:
>> Ideas on how to uniquely name the first and second set of "perc,
>> entry_date"?
> You can use alias-names for the 2 queries, like:
> test=*# select foobar.a as x, foobar.b as y, bar.* from (select * from foo()) foobar, (select * from foo()) bar ;
You don't really need the sub-selects: you can put aliases on functions
in FROM.
select * from foo(...) as f1(a,b), foo(...) as f2(x,y);
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > Andreas Kretschmer <akretschmer@spamfence.net> writes: > > Scott Serr <serrs@theserrs.net> wrote: > >> Ideas on how to uniquely name the first and second set of "perc, > >> entry_date"? > > > You can use alias-names for the 2 queries, like: > > > test=*# select foobar.a as x, foobar.b as y, bar.* from (select * from foo()) foobar, (select * from foo()) bar ; > > You don't really need the sub-selects: you can put aliases on functions > in FROM. > > select * from foo(...) as f1(a,b), foo(...) as f2(x,y); Right, thx, blackout ... Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On 11/07/2010 08:53 AM, Tom Lane wrote:
> Andreas Kretschmer<akretschmer@spamfence.net> writes:
>> Scott Serr<serrs@theserrs.net> wrote:
>>> Ideas on how to uniquely name the first and second set of "perc,
>>> entry_date"?
>> You can use alias-names for the 2 queries, like:
>> test=*# select foobar.a as x, foobar.b as y, bar.* from (select * from foo()) foobar, (select * from foo()) bar ;
> You don't really need the sub-selects: you can put aliases on functions
> in FROM.
>
> select * from foo(...) as f1(a,b), foo(...) as f2(x,y);
>
> regards, tom lane
>
Thanks Tom, Andreas, and Osvaldo...
I've found I really need these on the Select part rather than the From.
select otherstuff.*, foo(...) as f1(a,b), foo(...) as f2(x,y) from
otherstuff;
...won't work. It says:
subquery must return only one column
Funny thing is
select (foo(...)).*, (foo(...)).*;
...works fine, just has duplicate column names, so they are hard to get at.
I modeled this after examples here
http://www.postgresonline.com/journal/index.php?/archives/129-Use-of-OUT-and-INOUT-Parameters.html
I really only want a single record back from my function, but the
multi-record return looked easier. I need some kind of control of how
the columns are named per call.
Maybe this isn't possible... not alot of docs in this area.
Thanks,
Scott
2010/11/7 Scott Serr <serrs@theserrs.net>: > On 11/07/2010 08:53 AM, Tom Lane wrote: >> >> Andreas Kretschmer<akretschmer@spamfence.net> writes: >>> >>> Scott Serr<serrs@theserrs.net> wrote: >>>> >>>> Ideas on how to uniquely name the first and second set of "perc, >>>> entry_date"? >>> >>> You can use alias-names for the 2 queries, like: >>> test=*# select foobar.a as x, foobar.b as y, bar.* from (select * from >>> foo()) foobar, (select * from foo()) bar ; >> >> You don't really need the sub-selects: you can put aliases on functions >> in FROM. >> >> select * from foo(...) as f1(a,b), foo(...) as f2(x,y); >> >> regards, tom lane >> > > Thanks Tom, Andreas, and Osvaldo... > > I've found I really need these on the Select part rather than the From. > > select otherstuff.*, foo(...) as f1(a,b), foo(...) as f2(x,y) from > otherstuff; > ...won't work. It says: > subquery must return only one column > > Funny thing is > select (foo(...)).*, (foo(...)).*; > ...works fine, just has duplicate column names, so they are hard to get at. Attention: this syntax is great, but function is evaluated for every column one times! Regards Pavel Stehule > > I modeled this after examples here > http://www.postgresonline.com/journal/index.php?/archives/129-Use-of-OUT-and-INOUT-Parameters.html > > I really only want a single record back from my function, but the > multi-record return looked easier. I need some kind of control of how the > columns are named per call. > > Maybe this isn't possible... not alot of docs in this area. > > Thanks, > Scott > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >