Writing Functions - PostgreSQL: Up and Running - page 144

$$
LANGUAGE language_of_function
Argument names are optional, but if the arguments are not named, you cannot call
them using the arg1 := ... argument syntax. Arguments can also take a default value,
which makes the argument optional. Optional arguments should be positioned after
nonoptional arguments.
Functional definitions often include additional qualifiers to optimize execution and to
enforce security:
LANGUAGE
The language must be one installed in your database. Obtain a list with the SELECT
lanname FROM pg_language; query.
VOLATILITY
This setting clues the query planner into whether outputs can be cached and used
across multiple calls. Your choices are:
IMMUTABLE
The function will always return the same output for the same input.
STABLE
The function will return the same value for the same inputs within the same
query.
VOLATILE
The function can return different values with each call, even with the same
inputs. Functions that change data or depend on environment settings like
system time should be marked as VOLATILE . This is the default.
Keep in mind that the volatility setting is merely a hint to the planner. The
default value of VOLATILE ensures that the planner will always recompute the
plan. If you use one of the other values, the planner can still choose to forgo
caching if it decides that recomputing is more cost-effective.
STRICT
A strict function will always return NULL if any inputs are NULL , and the planner
in that case will skip evaluating the function altogether. Unless the function is
adorned with the STRICT qualifier, the query planner deems it not to be strict.
When writing SQL functions, be careful when using STRICT , because it could
prevent the planner from taking advantage of indexes. Read our aticle STRICT
on SQL Functions for more details.
COST
This is a relative measure of computational intensiveness. SQL and PL/pgSQL
functions default to 100 and C functions to 1 . This affects the order that the planner
will follow when evaluating functions in a WHERE clause, and the likelihood of cach‐