Re: Function returning 2 columns evaluated twice when both columns are needed

2009/10/21 Merlin Moncure <mmoncure@gmail.com>:
> On Tue, Oct 20, 2009 at 5:12 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>> [ shrug... ]  There are other possible reasons why the planner would
>>> fail to flatten a subquery, but none of them apply to the example you
>>> showed.  And your example function *was* VOLATILE, by default.
>>
>> I checked this on 8.5 and function is evaluated more time although is immutable.
>>
>> postgres=# create or replace function foo(out a int, out b int)
>> returns record as $$
>> begin
>> raise notice 'start foo';
>> a := 10; b := 20;
>> return;
>> end;
>> $$ language plpgsql immutable;
>> CREATE FUNCTION
>>
>> postgres=# select (foo()).*;
>
> This is because select (func()).* is expanded to mean:
> select func(f1), func(f2) ... func(fn);
>
> This is a general issue with '*' because in postgres it means:
> 'evaluate me for each field of me', not 'return all fields of me'. I
> don't think our behavior in this regard is correct (afaict i'm in the
> minority though).
>

I understand to this mechanism. This is only correction some previous
messages. This behave isn't depend on function immutability or
volatility. But I agree with you, so this is really problem - it is
very silent. Maybe we could to raise some warning or we could to move
funccall to subselect

like
SELECT (foo()).* to SELECT ( (SELECT foo()) ).*

Regards
Pavel Stehule

> merlin
>