Re: BUG #5611: SQL Function STABLE promoting to VOLATILE

Brian Ceccarelli <bceccarelli@net32.com> writes:
> OK.   The documentation says "allows the optimizer to optimize . . . ."    But then the example guarantees the
one-time-onlyfor a index scan condition.    

No, the documentation states that *if* an index scan is used, functions
involved in the indexscan's qual condition will be evaluated just once,
rather than once per row.  There is no "guarantee" of any sort that such
a plan will be chosen.  The point of the STABLE marking is to inform the
optimizer that it is safe to choose an index scan because the function's
results will not change compared to the naive SQL semantics wherein the
WHERE condition is evaluated for each row.  Thus, the guarantee actually
runs the other way: you are promising the optimizer that your function
doesn't have side effects or change its results intra-query.

            regards, tom lane