Thread: Selecting from a function(x,y) returning a row-type(sum, prod)

Hi,

I'm trying to select data from a table, converting two values and return
all four. Maybe this is best explained with an example:

The function from the documentation on pl/pgsql:

CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
     sum := x + y;
     prod := x * y;
END;
$$ LANGUAGE plpgsql;

And a table 'myvals' with x and y integer values:
CREATE TABLE myvals (INT x, INT y);


How can I do the following:

select * from myvals, sum_n_product(myvals.x, myvals.y);

Here I get an error:
ERROR:  function expression in FROM may not refer to other relations of
same query level

What I want is a view with the values:

x | y | sum | prod |


Best regards,

Heiko

am  Mon, dem 30.04.2007, um 15:45:18 +0200 mailte Heiko Klein folgendes:
> Hi,
>
> I'm trying to select data from a table, converting two values and return
> all four. Maybe this is best explained with an example:
>
> The function from the documentation on pl/pgsql:
>
> CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
> BEGIN
>     sum := x + y;
>     prod := x * y;
> END;
> $$ LANGUAGE plpgsql;
>
> And a table 'myvals' with x and y integer values:
> CREATE TABLE myvals (INT x, INT y);
>
>
> How can I do the following:
>
> select * from myvals, sum_n_product(myvals.x, myvals.y);

select x, y, sum_n_product(x,y) from myvals;


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

"A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> am  Mon, dem 30.04.2007, um 15:45:18 +0200 mailte Heiko Klein folgendes:
>> How can I do the following:
>>
>> select * from myvals, sum_n_product(myvals.x, myvals.y);

> select x, y, sum_n_product(x,y) from myvals;

This is only part of the answer, however, because what you get is

regression=# select *, sum_n_product(x,y) from myvals;
 x | y | sum_n_product
---+---+---------------
 1 | 2 | (3,2)
(1 row)

which is not the display he wanted.  If you know a little bit about how
PG deals with *-expansion you might think to try

regression=# select *, (sum_n_product(x,y)).* from myvals;
 x | y | sum | prod
---+---+-----+------
 1 | 2 |   3 |    2
(1 row)

which is the correct output --- but it turns out that what it's doing is
effectively

select *, (sum_n_product(x,y)).sum, (sum_n_product(x,y)).prod from myvals;

ie the function is called twice per row.  If that's a problem, what you
have to do is resort to a two-level query:

regression=# select x,y,(f).* from
regression-#   (select *, sum_n_product(x,y) as f from myvals offset 0) ss;
 x | y | sum | prod
---+---+-----+------
 1 | 2 |   3 |    2
(1 row)

The "offset 0" is an optimization fence to keep the planner from
flattening this form into the form where the function is called twice.
(As of 8.2, you can dispense with that if the function is marked volatile.)

            regards, tom lane

Tom Lane wrote:
> "A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
>> am  Mon, dem 30.04.2007, um 15:45:18 +0200 mailte Heiko Klein folgendes:
>>> How can I do the following:
>>>
>>> select * from myvals, sum_n_product(myvals.x, myvals.y);
>
>> select x, y, sum_n_product(x,y) from myvals;

> regression=# select x,y,(f).* from
> regression-#   (select *, sum_n_product(x,y) as f from myvals offset 0) ss;

If you really just want to calculate product and sum, it may be easier
to write just:

select x, y, x + y as sum, x * y as prod from myvals;

No need for fancy SP's in that case.
--
Alban Hertroys

Thanks,

my real function is quite expensive, so I don't want it to execute
twice. Toms subselect query is therefore exactly what I want, and since
it will be hidden in a view, it doesn't matter that it is a long expression.

Best regards,

Heiko

Tom Lane wrote:

> regression=# select x,y,(f).* from
> regression-#   (select *, sum_n_product(x,y) as f from myvals offset 0) ss;
>  x | y | sum | prod
> ---+---+-----+------
>  1 | 2 |   3 |    2
> (1 row)
>
> The "offset 0" is an optimization fence to keep the planner from
> flattening this form into the form where the function is called twice.
> (As of 8.2, you can dispense with that if the function is marked volatile.)
>