Scott Serr <serrs@xxxxxxxxxxxx> 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° -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
