Обсуждение: function with multiple return values

Поиск
Список
Период
Сортировка

function with multiple return values

От
Scott Serr
Дата:
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


Re: function with multiple return values

От
Andreas Kretschmer
Дата:
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°

Re: function with multiple return values

От
Tom Lane
Дата:
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

Re: function with multiple return values

От
Andreas Kretschmer
Дата:
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°

Re: function with multiple return values

От
Scott Serr
Дата:
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



Re: function with multiple return values

От
Pavel Stehule
Дата:
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
>