Hello
2009/10/19 Tom Lane <tgl@sss.pgh.pa.us>:
> Gerhard Wiesinger <lists@wiesinger.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>