Re: Convincing STABLE functions to run once

Dan Wells <dbw2(at)calvin(dot)edu> writes:
> I've run into this issue in several contexts recently, and wonder if
> folks here can help clear up my understanding of function volatility. I
> often have functions which are not truly immutable (they do something
> minor, like read in configuration information), but the functions
> themselves are fairly expensive, so I want them to run just once per
> query. At face value, I feel like STABLE should do what I want, but
> often it does not.

STABLE tells the system it's *okay* to run the function fewer times than
naive SQL semantics might suggest. There's no *guarantee* that any such
optimization will happen (and in fact, about the only special thing that
currently happens for STABLE functions is that they're considered okay
to use in indexscan qualifications).

What I'd suggest is sticking the expensive function call into a CTE
(a WITH clause). We do guarantee only-once eval for CTEs.

regards, tom lane