Re: optimiser STABLE vs. temp table was: HOWTO caching data across function calls: temporary tables, cursor?

On Tue, 01 Apr 2008 11:22:20 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ivan Sergio Borgonovo <mail(at)webthatworks(dot)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