Re: Stored procedure returning row or resultset

Richard Huxton <dev(at)archonet(dot)com> writes:
> On Monday 14 Oct 2002 6:17 pm, Tom Lane wrote:
>> Unfortunately that's not true at all, or at least not helpful for this
>> problem. The cachable attribute was poorly named, because it leads
>> people to think that PG *will* cache function results, as opposed to
>> *could* cache function results.

> I must admit, that was my impression. Are there simple rules for if/when PG
> will cache function results?

It won't; there is no function cache. What there is is a pass of
constant-folding before a query is run. For example, if you write

select * from foo where x > sqrt(4);

then the function call "sqrt(4)" will be folded down to a constant "2"
before planning and execution starts, rather than evaluating it again
at each row of foo. (This also improves the system's ability to use
indexes, etc, so it's a pretty essential thing.)

The point of the poorly-named isCachable attribute is to tell the
constant-folding pass whether it's safe to apply the function in
advance of execution --- ie, does it always return the same output,
given constant inputs? An example of a non-cachable function is
now().

In 7.3 isCachable has been split into two attributes "immutable"
and "stable", distinguishing functions that are constant for all
time from those whose outputs are constant during any single query.
(sqrt() is immutable, now() is stable, random() is neither.)
These names perhaps will be less likely to mislead people into
thinking that some kind of caching goes on while a query runs.

regards, tom lane