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

On Tue, Apr 01, 2008 at 06:06:35PM +0200, Ivan Sergio Borgonovo wrote:
> Would you please be so kind to rephrase:
>
> http://www.postgresql.org/docs/8.1/interactive/xfunc-volatility.html

<snip>

> 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?

Yes, it's limited to a single statememnt. Let's say hypothetically
postgres could optimise:

select f() + f();

to

select 2*f();

It would have to optimised so it only calls the function once yet at no
time does it "cache" the result in the usual sense.

Does this helps,
> 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?

Yes.

> So... then any pointer to some places where I could learn some
> caching techniques if STABLE doesn't do the trick?

Anything persistnat usually needs to be in a table. If it's a really
small amount you could use the global namespace in pl/perl or similar in
other languages.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.