Re: [GENERAL] function with multiple return values
2010/11/7 Scott Serr : > On 11/07/2010 08:53 AM, Tom Lane wrote: >> >> Andreas Kretschmer writes: >>> >>> Scott Serr 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 > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function with multiple return values
On 11/07/2010 08:53 AM, Tom Lane wrote: Andreas Kretschmer writes: Scott Serr 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function with multiple return values
Tom Lane wrote: > Andreas Kretschmer writes: > > Scott Serr 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° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function with multiple return values
Andreas Kretschmer writes: > Scott Serr 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] function with multiple return values
Scott Serr 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 > FROMt1, 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° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 FROMt1, 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
