Re: how to evaluate a function only once for a query? - Mailing list pgsql-general

Jeff Eckermann <jeff_eckermann@yahoo.com> writes:
> The only way that "iscachable" would prevent the need
> for reevaluation on every row is by permitting the
> creation of an index on the function return values

As far as I could see, he wanted the system to pre-evaluate a call
of the function with a literal-constant argument --- which is exactly
what isCachable is all about.

You're correct that in a case like

    select ... where myfunc(field1) = 'constant';

the only thing that will help is an index on myfunc(field1).  But this
case is quite different from

    select ... where field1 = myfunc('constant');

BTW, for 7.3 the name "isCachable" will be deprecated; we now recommend
"immutable" for the same concept.  Hopefully this will serve to avoid
some confusion.  See the development docs for CREATE FUNCTION at

http://developer.postgresql.org/docs/postgres/sql-createfunction.html

            regards, tom lane