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

Hello

2009/10/19 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Gerhard Wiesinger <lists(at)wiesinger(dot)com> writes:
>> On Mon, 19 Oct 2009, Tom Lane wrote:
>>> Probably because you have the function declared VOLATILE.
>
>> None of the function is declared VOLATILE. Any other idea?
>
> [ 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()).*;
NOTICE: start foo
NOTICE: start foo
a │ b
────┼────
10 │ 20
(1 row)

I was surprised, there are necessary subselect, but "offset" is optional:

postgres=# select (foo).* from (select foo()) f;
NOTICE: start foo
a │ b
────┼────
10 │ 20
(1 row)

postgres=# select (foo).* from (select foo() offset 0) f;
NOTICE: start foo
a │ b
────┼────
10 │ 20
(1 row)

regards
Pavel Stehule
>
>                        regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>