> >What Philip seems to be asking for is a mechanism where by if a function
> >is marked as being mathematically deterministic (given a
> particular set of
> >parameters the same result is always returned -- eg: cos(), sin(),
> >etc) then the result is cached and next time the function is called with
> >the same argument(s) the result is retrieved from the cache
> instead of the
> >function being run again.
>
> Exactly. But obviously not limited to simple mathematical functions.
>From 7.3 docs:
http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-createfunction.html
CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] ) RETURNS rettype { LANGUAGE langname |
IMMUTABLE| STABLE | VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [EXTERNAL]
SECURITYINVOKER | [EXTERNAL] SECURITY DEFINER | AS 'definition' | AS 'obj_file', 'link_symbol' } ...
[ WITH ( attribute [, ...] ) ]
And:
IMMUTABLE
STABLE
VOLATILE
These attributes inform the system whether it is safe to replace multiple
evaluations of the function with a single evaluation, for run-time
optimization. At most one choice should be specified. If none of these
appear, VOLATILE is the default assumption.
IMMUTABLE indicates that the function always returns the same result when
given the same argument values; that is, it does not do database lookups or
otherwise use information not directly present in its parameter list. If
this option is given, any call of the function with all-constant arguments
can be immediately replaced with the function value.
STABLE indicates that within a single table scan the function will
consistently return the same result for the same argument values, but that
its result could change across SQL statements. This is the appropriate
selection for functions whose results depend on database lookups, parameter
variables (such as the current time zone), etc. Also note that the
CURRENT_TIMESTAMP family of functions qualify as stable, since their values
do not change within a transaction.
VOLATILE indicates that the function value can change even within a single
table scan, so no optimizations can be made. Relatively few database
functions are volatile in this sense; some examples are random(), currval(),
timeofday(). Note that any function that has side-effects must be classified
volatile, even if its result is quite predictable, to prevent calls from
being optimized away; an example is setval().
So it seems Philip already has what he wants?
Chris
