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

On Tue, 1 Apr 2008 16:08:45 +0200
"Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

[snip]

> Since you are looking for a sample, maybe something like that can
> get you started:
>
> CREATE SEQUENCE temp_names;

[snip]

I was thinking something in the line of it.
I wasn't that sure of the course to follow.
Just few hours ago it looked like a swamp. Thanks, now I've more
confidence it is a feasible plan.

> > For me it's not clear if adding some additional caching system
> > (eg. create a temp table inside the function) would obtain the
> > same result as marking the function STABLE.

> No, STABLE doen't help here - that is only a hint for the optimizer.

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.

I'd say the optimiser will try to cache it unless it finds something
better to do with the memory.
If that's what happens I think I'll avoid to complicate my life with
a manual cache management and just wrap the most general query in a
function.

Can anybody confirm that's how the optimiser work or explain the
differences between providing a "manual" cache and just declaring a
function STABLE?

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it