FUNCTION#
Synopsis#
FUNCTION name ( [ parameter_name data_type [, ...] ] )
RETURNS type
[ LANGUAGE language]
[ NOT? DETERMINISTIC ]
[ RETURNS NULL ON NULL INPUT ]
[ CALLED ON NULL INPUT ]
[ SECURITY { DEFINER | INVOKER } ]
[ COMMENT description]
statements
Description#
Declare a SQL routine.
The name of the routine. Inline routines can use a simple
string.
The list of parameters is a comma-separated list of names parameter_name and
data types data_type, see data type. An empty list, specified as
() is also valid.
The type value after the RETURNS keyword identifies the data
type of the routine output.
The optional LANGUAGE characteristic identifies the language used for the
routine definition with language. Only SQL is supported.
The optional DETERMINISTIC or NOT DETERMINISTIC characteristic declares that
the routine is deterministic. This means that repeated routine calls with
identical input parameters yield the same result. For SQL language routines, a
routine is non-deterministic if it calls any non-deterministic routines and
functions. By default, routines are assume to have a deterministic
behavior.
The optional RETURNS NULL ON NULL INPUT characteristic declares that the
routine returns a NULL value when any of the input parameters are NULL.
The routine is not invoked with a NULL input value.
The CALLED ON NULL INPUT characteristic declares that the routine is invoked
with NULL input parameter values.
The RETURNS NULL ON NULL INPUT and CALLED ON NULL INPUT characteristics are
mutually exclusive, with CALLED ON NULL INPUT as the default.
The security declaration of SECURITY INVOKER or SECURITY DEFINER is only
valid for catalog routines. It sets the mode for processing the routine with the
permissions of the user who calls the routine (INVOKER) or the user who
created the routine (DEFINER).
The COMMENT characteristic can be used to provide information about the
function to other users as description. The information is accessible with
SHOW FUNCTIONS.
The body of the routine can either be a simple single RETURN statement with an
expression, or compound list of statements in a BEGIN block. Routines must
contain a RETURN statement at the end of the top-level block, even if it’s
unreachable.
Examples#
A simple catalog function:
CREATE FUNCTION meaning_of_life()
RETURNS BIGINT
RETURN 42;
And used:
SELECT meaning_of_life(); -- returns 42
Equivalent usage with an inline function:
WITH FUNCTION meaning_of_life()
RETURNS BIGINT
RETURN 42
SELECT meaning_of_life();
Further examples of varying complexity that cover usage of the FUNCTION
statement in combination with other statements are available in the SQL
routines examples documentation.
