Hello 2009/10/19 Tom Lane <tgl@xxxxxxxxxxxxx>: > Gerhard Wiesinger <lists@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
