Re: Selecting from a function(x,y) returning a row-type(sum, prod) - Mailing list pgsql-general

"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