function with multiple return values

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