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

On Wed, Oct 21, 2009 at 12:37 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2009/10/21 Merlin Moncure <mmoncure(at)gmail(dot)com>:
>> On Tue, Oct 20, 2009 at 5:12 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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()) ).*

If we are going to change I think ultimately the best answer is that
'(me).*' should mean: 'return all the fields of me', whatever 'me' is,
meaning that:

create view v as select (foo).* from foo;

should now change the definition of v if we add a column to foo. As opposed to:

create view v as select * from foo;

which would not (and shouldn't).

merlin