On Tue, 01 Apr 2008 11:22:20 -0400 Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx> writes: > > I can't really appreciate the difference... or better... I think > > the difference may be that I can't take for granted the function > > will be cached if I delegate the choice to the optimiser. > You can take for granted that it won't be, because there is no > function cache in Postgres. Would you please be so kind to rephrase: http://www.postgresql.org/docs/8.1/interactive/xfunc-volatility.html "A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. This category allows the optimizer to optimize multiple calls of the function to a single call. In particular, it is safe to use an expression containing such a function in an index scan condition. (Since an index scan will evaluate the comparison value only once, not once at each row, it is not valid to use a VOLATILE function in an index scan condition.)" I can't understand how it can call a function a single time and avoid to cache the result. Is it limited to a single statement? eg. create or replace function(...) as $$ begin select into bau cetti from stablefunc(sameparam); select into bingo t.bongo from stablefunc(sameparam) as s join sometable t on s.cetti=t.cetti; ... will call stablefunc 2 times? I do appreciate the difference between: create or replace function(...) as $$ begin select into bau cetti from stablefunc(sameparam); insert into sometable... select into bingo t.bongo from stablefunc(sameparam) as s join sometable t on s.cetti=t.cetti; since stablefunc may depend on sometable and it is going to see the change. So... then any pointer to some places where I could learn some caching techniques if STABLE doesn't do the trick? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
