From: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: function with multiple return values |
Date: | 2010-11-07 09:43:10 |
Message-ID: | 20101107094310.GA12906@tux |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Scott Serr <serrs(at)theserrs(dot)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°
From | Date | Subject | |
---|---|---|---|
Next Message | Allan Kamau | 2010-11-07 12:51:38 | Looking for a Generic lightweight job queueing (stack) implementation. |
Previous Message | Anders Söderman | 2010-11-07 07:52:48 | Run "postgresql\9.0\data" on a RAID-1 disk on my Local Area Network (LAN) |