The CREATE FUNCTION (Sourced or Template) statement is used to register a function or function template with a server.
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
The privileges held by the authorization ID of the statement must also include EXECUTE privilege on the source function if the authorization ID of the statement does not have DATAACCESS authority and the SOURCE clause is specified.
Group privileges are not considered for any table or view specified in the CREATE FUNCTION statement.
>>-CREATE FUNCTION--function-name-------------------------------> >--(--+-------------------------------+--)--●-------------------> | .-,-------------------------. | | V | | '---| parameter-declaration |-+-' >--RETURNS--| data-type2 |--●--+-------------------------+--●---> '-SPECIFIC--specific-name-' >--+-SOURCE--+-function-name--------------------------+--+------------------------------+-+--> | +-SPECIFIC--specific-name----------------+ '-PARAMETER CCSID--+-ASCII---+-' | | '-function-name--(--+---------------+--)-' '-UNICODE-' | | | .-,---------. | | | | V | | | | '---data-type-+-' | | .-NOT DETERMINISTIC-. .-EXTERNAL ACTION----. | '-AS TEMPLATE--●--+-------------------+--●--+--------------------+---------------------' '-DETERMINISTIC-----' '-NO EXTERNAL ACTION-' >--●----------------------------------------------------------->< parameter-declaration |--+----------------+--| data-type1 |--+--------------------+---| '-parameter-name-' '-| default-clause |-' data-type1, data-type2 |--+-| built-in-type |----+-------------------------------------| +-distinct-type-name---+ '-structured-type-name-' built-in-type |--+-+-SMALLINT----+----------------------------------------------------------------------+--| | +-+-INTEGER-+-+ | | | '-INT-----' | | | '-BIGINT------' | | .-(5,0)-------------------. | +-+-+-DECIMAL-+-+--+-------------------------+-----------------------------------------+ | | '-DEC-----' | | .-,0-------. | | | '-+-NUMERIC-+-' '-(integer-+----------+-)-' | | '-NUM-----' '-,integer-' | | .-(53)------. | +-+-FLOAT--+-----------+--+------------------------------------------------------------+ | | '-(integer)-' | | | +-REAL------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+-' | | .-(34)-. | +-DECFLOAT--+------+-------------------------------------------------------------------+ | '-(16)-' | | .-(1)------------------------. | +-+-+-+-CHARACTER-+--+----------------------------+----------+--+------------------+-+-+ | | | '-CHAR------' '-(integer-+-------------+-)-' | | (1) | | | | | | +-OCTETS------+ | '-FOR BIT DATA-----' | | | | | '-CODEUNITS32-' | | | | | '-+-VARCHAR----------------+--(integer-+-------------+-)-' | | | | '-+-CHARACTER-+--VARYING-' +-OCTETS------+ | | | | '-CHAR------' '-CODEUNITS32-' | | | | .-(1M)-----------------------------. | | | '-+-CLOB------------------------+--+----------------------------------+------------' | | '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-+-------------+-)-' | | '-CHAR------' +-K-+ +-OCTETS------+ | | +-M-+ '-CODEUNITS32-' | | '-G-' | | .-(1)------------------------. | +-+-GRAPHIC--+----------------------------+------+-------------------------------------+ | | '-(integer-+-------------+-)-' | | | | +-CODEUNITS16-+ | | | | '-CODEUNITS32-' | | | +-VARGRAPHIC--(integer-+-------------+-)-------+ | | | +-CODEUNITS16-+ | | | | '-CODEUNITS32-' | | | | .-(1M)-----------------------------. | | | '-DBCLOB--+----------------------------------+-' | | '-(integer-+---+-+-------------+-)-' | | +-K-+ +-CODEUNITS16-+ | | +-M-+ '-CODEUNITS32-' | | '-G-' | | .-(1)-------. | +-+-+-+-NCHAR-------------------+--+-----------+------+-------+------------------------+ | | | '-NATIONAL--+-CHAR------+-' '-(integer)-' | | | | | | '-CHARACTER-' | | | | | '-+-NVARCHAR-------------------------+--(integer)-' | | | | +-NCHAR VARYING--------------------+ | | | | '-NATIONAL--+-CHAR------+--VARYING-' | | | | '-CHARACTER-' | | | | .-(1M)-------------. | | | '-+-NCLOB---------------------------+--+------------------+-' | | +-NCHAR LARGE OBJECT--------------+ '-(integer-+---+-)-' | | '-NATIONAL CHARACTER LARGE OBJECT-' +-K-+ | | +-M-+ | | '-G-' | | .-(1M)-------------. | +-+-BLOB----------------+--+------------------+----------------------------------------+ | '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------------+-----------------------------------------------------+ | +-TIME-------------------------+ | | | .-(--6--)-------. | | | '-TIMESTAMP--+---------------+-' | | '-(--integer--)-' | +-XML----------------------------------------------------------------------------------+ | .-SYSPROC.-. (2) (3) | '-+----------+--DB2SECURITYLABEL-------------------------------------------------------' default-clause |--DEFAULT--+-NULL-------------+--------------------------------| +-constant---------+ +-special-register-+ +-global-variable--+ '-(--expression--)-'
The name, including the implicit or explicit qualifiers, together with the number of parameters and the data type of each parameter (without regard for any length, precision or scale attributes of the data type) must not identify a function or function template described in the catalog (SQLSTATE 42723). The unqualified name, together with the number and data types of the parameters, while of course unique within its schema, need not be unique across schemas.
If a two-part name is specified, the schema-name cannot begin with 'SYS' (SQLSTATE 42939).
A number of names used as keywords in predicates are reserved for system use, and cannot be used as a function-name (SQLSTATE 42939). The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH, and the comparison operators.
When naming a user-defined function that is sourced on an existing function with the purpose of supporting the same function with a user-defined distinct type, the same name as the sourced function may be used. This allows users to use the same function with a user-defined distinct type without realizing that an additional definition was required. In general, the same name can be used for more than one function if there is some difference in the signature of the functions.
CREATE FUNCTION WOOFER() ...
No two identically-named functions within a schema are permitted to have exactly the same type for all corresponding parameters. This restriction also applies to a function and function template with the same name within the same schema. Lengths, precisions, and scales are not considered in this type comparison. Therefore, CHAR(8) and CHAR(35) are considered to be the same type, as are DECIMAL(11,2) and DECIMAL (4,3). A weakly typed distinct type specified for a parameter is considered to be the same data type as the source type of the distinct type. For a Unicode database, CHAR(13) and GRAPHIC(8) are considered to be the same type. There is some further bundling of types that causes them to be treated as the same type for this purpose, such as DECIMAL and NUMERIC. A duplicate signature returns an error (SQLSTATE 42723).
Any valid SQL data type can be used if it is castable to the type of the corresponding parameter of the function identified in the SOURCE clause (for information, see "Casting between data types"). However, this checking does not guarantee that an error will not occur when the function is invoked.
For a more complete description of each built-in data type, see "CREATE TABLE".
For a user-defined distinct type, the length, precision, or scale attributes for the parameter are those of the source type of the distinct type (those specified on CREATE TYPE). A distinct type parameter is passed as the source type of the distinct type. If the name of the distinct type is unqualified, the database manager resolves the schema name by searching the schemas in the SQL path.
For a user-defined structured type, the appropriate transform functions must exist in the associated transform group.
Because the function is sourced, it is not necessary (but still permitted) to specify length, precision, or scale for the parameterized data types. Empty parentheses can be used instead; for example, CHAR(). A parameterized data type is any one of the data types that can be defined with a specific length, scale, or precision. The parameterized data types are the string data types, the decimal data types, and the TIMESTAMP data type.
With a function template, empty parentheses can also be used instead of specifying length, precision, or scale for the parameterized data types. It is recommended to use empty parentheses for the parameterized data types. If you use empty parentheses, the length, precision, or scale is the same as that of the remote function, which is determined when the function template is mapped to a remote function by creating a function mapping. If you omit parentheses altogether, the default length for the data type is used (see "CREATE TABLE").
The expression can be any expression of the type described in "Expressions". If a default value is not specified, the parameter has no default and the corresponding argument cannot be omitted on invocation of the procedure. The maximum size of the expression is 64K bytes.
The default expression must not modify SQL data (SQLSTATE 428FL or SQLSTATE 429BL). The expression must be assignment compatible to the parameter data type (SQLSTATE 42821).
A default cannot be specified for a parameter of type ARRAY, ROW, or CURSOR (SQLSTATE 429BB).
With a sourced scalar function, any valid SQL data type is acceptable, as is a distinct type, provided it is castable from the result type of the source function. An array type cannot be specified as the data type of a parameter (SQLSTATE 42879).
The parameter of a parameterized type need not be specified for parameters of a sourced function. Instead, empty parentheses can be used; for example, VARCHAR().
For additional considerations and rules that apply to the specification of the data type in the RETURNS clause when the function is sourced on another, see the "Rules" section of this statement.
With a function template, empty parentheses are not allowed (SQLSTATE 42611). Length, precision, or scale must be specified for the parameterized data types. It is recommended to specify the same length, precision, or scale as that of the remote function.
The specific-name may be the same as an existing function-name.
If no qualifier is specified, the qualifier that was used for function-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier of function-name or an error (SQLSTATE 42882) is returned.
If specific-name is not specified, a unique name is generated by the database manager. The unique name is SQL followed by a character timestamp, SQLyymmddhhmmssxxx.
If an unqualified name is provided, then the current SQL path (the value of the CURRENT PATH special register) is used to locate the function. The first schema in the SQL path that has a function with this name for which the authorization ID of the statement has EXECUTE privilege is selected.
If no function by this name exists in the named schema or if the name is not qualified and there is no function with this name in the SQL path, an error (SQLSTATE 42704) is returned. If there is more than one authorized specific instance of the function in the named or located schema, an error (SQLSTATE 42725) is returned. If a function by this name exists and the authorization ID of the statement does not have EXECUTE privilege on this function, an error (SQLSTATE 42501) is returned.
If an unqualified name is provided, the current SQL path is used to locate the function. The first schema in the SQL path that has a function with this specific name for which the authorization ID of the statement has EXECUTE privilege is selected.
If no function by this specific-name exists in the named schema or if the name is not qualified and there is no function with this specific-name in the SQL path, an error (SQLSTATE 42704) is returned. If a function by this specific-name exists, and the authorization ID of the statement does not have EXECUTE privilege on this function, an error (SQLSTATE 42501) is returned.
The rules for function resolution are applied to select one function from the functions with the same function name, given the data types specified in the SOURCE clause. However, the data type of each parameter in the function selected must have the exact same type as the corresponding data type specified in the source function.
It is not necessary to specify the length, precision or scale for the parameterized data types. Instead an empty set of parentheses may be coded to indicate that these attributes are to be ignored when looking for a data type match. For example, DECIMAL() will match a parameter whose data type was defined as DECIMAL(7,2)).
FLOAT() cannot be used (SQLSTATE 42601), because the parameter value indicates different data types (REAL or DOUBLE).
However, if length, precision, or scale is coded, the value must exactly match that specified in the CREATE FUNCTION statement. This can be useful in assuring that the intended function will be used. Note also that synonyms for data types will be considered a match (for example DEC and NUMERIC will match).
A type of FLOAT(n) does not need to match the defined value for n, because 0<n<25 means REAL and 24<n<54 means DOUBLE. Matching occurs based on whether the type is REAL or DOUBLE.
If no function with the specified signature exists in the named or implied schema, an error (SQLSTATE 42883) is returned.
The PARAMETER CCSID clause must specify the same encoding scheme as the source function (SQLSTATE 53090).
A function that is not deterministic might receive incorrect results if it is executed by parallel tasks.
A function with external actions might return incorrect results if the function is executed by parallel tasks. For example, if the function sends a note for each initial call to it, one note is sent for each parallel task instead of once for the function.
An error (SQLSTATE 42613) will result from violation of these rules.
Instead, empty parentheses may be specified as part of the data type (for example: VARCHAR()) in order to indicate that the length/precision/scale will be the same as those of the source function, or determined by the casting.
However, if length, precision, or scale is specified then the value in CF is checked against the corresponding value in SF as outlined in the remaining rules for input parameters and returns value.
Note that this rule provides no guarantee against an error occurring when CF is used. An argument that matches the data type and length or precision attributes of a CF parameter may not be assignable if the corresponding SF parameter has a shorter length or less precision. In general, parameters of CF should not have length or precision attributes that are greater than the attributes of the corresponding SF parameters.
Note that this rule provides no guarantee against an error occurring when CF is used. A result value that matches the data type and length or precision attributes of the SF RETURNS data type may not be assignable if the CF RETURNS data type has a shorter length or less precision. Caution should be used when choosing to specify the RETURNS data type of CF as having length or precision attributes that are less than the attributes of the SF RETURNS data type.
The counterpart can be a function (scalar or source) or a function template. If the user creates a function and the required mapping, then, each time a query that specifies the function is processed, the database manager (1) compares strategies for invoking it with strategies for invoking the data source function, and (2) invokes the function that is expected to require less overhead.
If the user creates a function template and the mapping, then each time a query that specifies the template is processed, the database manager invokes the data source function that it maps to, provided that an access plan for invoking this function exists.
CREATE FUNCTION MYCENTRE (INTEGER, INTEGER)
RETURNS FLOAT
SOURCE PELLOW.CENTRE (INTEGER, FLOAT)
CREATE FUNCTION AVG (HATSIZE) RETURNS HATSIZE
SOURCE SYSIBM.AVG (INTEGER)
The creation
of the distinct type has generated the required cast function, allowing
the cast from HATSIZE to INTEGER for the argument and from INTEGER
to HATSIZE for the result of the function. CREATE FUNCTION NOVA.STATS (DOUBLE, DOUBLE)
RETURNS DOUBLE
AS TEMPLATE DETERMINISTIC NO EXTERNAL ACTION
CREATE FUNCTION BONUS ()
RETURNS DECIMAL (8,2)
AS TEMPLATE DETERMINISTIC NO EXTERNAL ACTION