On Tue, 01 Apr 2008 11:22:20 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ivan Sergio Borgonovo <mail@webthatworks.it> 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