CREATE FUNCTION (sourced or template) statement

Invocation

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).

Authorization

The privileges held by the authorization ID of the statement must also include EXECUTE privilege on the source function or EXECUTEIN privilege on the schema containing the source function if the authorization ID of the statement does not have DATAACCESS authority or DATAACCESS authority on the schema containing the source function and the SOURCE clause is specified.

Group privileges are not considered for any table or view specified in the CREATE FUNCTION statement.

Syntax

parameter-declaration

data-type1, data-type2

built-in-type

default-clause

Notes:

• ¹ The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).
• ² DB2SECURITYLABEL is the built-in distinct type that must be used to define the row security label column of a protected table.
• ³ For a column of type DB2SECURITYLABEL, NOT NULL WITH DEFAULT is implicit and cannot be explicitly specified (SQLSTATE 42842). The default value for a column of type DB2SECURITYLABEL is the session authorization ID's security label for write access.

Description

function-name

Names the function or function template being defined. It is a qualified or unqualified name that designates a function. The unqualified form of function-name is an SQL identifier. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The qualified form is a schema-name followed by a period and an SQL identifier.

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.

(parameter-declaration,...)

Identifies the number of input parameters of the function or function template, and specifies the data type and optional default value of each parameter. One entry in the list must be specified for each parameter that the function or function template will expect to receive. No more than 90 parameters are allowed (SQLSTATE 54023).

It is possible to register a function that has no parameters. In this case, the parentheses must still be coded, with no intervening data types. For example:

   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).

parameter-name

Specifies an optional name for the input parameter. The name cannot be the same as any other parameter-name in the parameter list (SQLSTATE 42734).

data-type1

Specifies the data type of the input parameter. The data type can be a built-in data type, a distinct type, or a structured type.

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.

• A datetime type parameter is passed as a character data type, and the data is passed in the ISO format.
• Array types cannot be specified (SQLSTATE 42879).
• A reference type specified as REF(type-name) cannot be specified (SQLSTATE 42879).

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).

DEFAULT

Specifies a default value for the parameter. The default can be a constant, a special register, a global variable, an expression, or the keyword NULL. The special registers that can be specified as the default are that same as those that can be specified for a column default (see default-clause in the CREATE TABLE statement). Other special registers can be specified as the default by using an expression.

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).

RETURNS

This mandatory clause identifies the output of the function or function template.

data-type2

Specifies the data type of the output.

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.

built-in-type

See "CREATE TABLE" for the description of built-in data types.

SPECIFIC specific-name

Provides a unique name for the instance of the function that is being defined. This specific name can be used when sourcing on this function, dropping the function, or commenting on the function. It can never be used to invoke the function. The unqualified form of specific-name is an SQL identifier. The qualified form is a schema-name followed by a period and an SQL identifier. The name, including the implicit or explicit qualifier, must not identify another function instance that exists at the application server; otherwise an error (SQLSTATE 42710) is returned.

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.

SOURCE

Specifies that the new function is being defined as a sourced function. A sourced function is implemented by another function (the source function). The function must be a scalar or aggregate function that exists at the current server, and it must be one of the following types of functions:

• A function that was defined with a CREATE FUNCTION statement
• A cast function that was generated by a CREATE TYPE statement
• A built-in function

If the source function is not a built-in function, the particular function can be identified by its name, function signature, or specific name.

If the source function is a built-in function, the SOURCE clause must include a function signature for the built-in function. The source function must not be any of the following built-in functions (If a particular syntax is indicated, only the indicated form cannot be specified.):

• CARDINALITY
• CHAR when more than one parameter is specified and the first parameter is a datetime data type
• CHARACTER_LENGTH with the string units parameter
• COALESCE
• CONTAINS
• CURSOR_ROWCOUNT
• DATAPARTITIONNUM
• DBPARTITIONNUM
• DEREF
• EXTRACT
• GRAPHIC when more than one parameter is specified and the first parameter is a datetime data type
• GREATEST
• HASHEDVALUE
• INSERT with the string units parameter
• INSTR with the string units parameter
• LCASE with the string units parameter
• LEAST
• LEFT with the string units parameter
• LENGTH with the string units parameter
• LOCATE with the string units parameter
• LOCATE_IN_STRING with the string units parameter
• LOWER with the string units parameter
• MAX
• MAX_CARDINALITY
• MIN
• NODENUMBER
• NULLIF
• NVL
• OVERLAY with the string units parameter
• PARAMETER
• POSITION with the string units parameter
• RAISE_ERROR
• REC2XML
• RID
• RID_BIT
• RIGHT with the string units parameter
• SCORE
• STRIP
• SUBSTRING with the string units parameter
• TRIM
• TRIM_ARRAY
• TYPE_ID
• TYPE_NAME
• TYPE_SCHEMA
• UCASE with the string units parameter
• UPPER with the string units parameter
• VALUE
• VARCHAR when more than one parameter is specified and the first parameter is a datetime data type
• VARGRAPHIC when more than one parameter is specified and the first parameter is a datetime data type
• XMLATTRIBUTES
• XMLCOMMENT
• XMLCONCAT
• XMLDOCUMENT
• XMLELEMENT
• XMLFOREST
• XMLNAMESPACES
• XMLPARSE
• XMLPI
• XMLQUERY
• XMLROW
• XMLSERIALIZE
• XMLTEXT
• XMLVALIDATE
• XMLXSROBJECTID
• XSLTRANSFORM

function-name

Identifies the function to use as the source. Valid only if this name is unique within the schema and the authorization ID has DATAACCESS authority on the schema, EXECUTEIN privilege on the schema, or EXECUTE privilege on the function. This syntax variant is not valid for a source function that is a built-in function.

If you provide an unqualified name, the SQL path will be used to locate the function. This is the value of the CURRENT PATH special register. The first schema in the SQL path that has this function name and whose authorization ID has DATAACCESS authority on the schema, EXECUTEIN privilege on the schema, or EXECUTE privilege on the function is selected.

The database will return error SQLSTATE 42704 for each of the following cases:

• no function by this name exists in the named schema
• the name is not valid
• there is no function with this name in the SQL path

The database will return error SQLSTATE 42725 if there is more than one authorized instance of the function in the named or located schema.

The database will return error SQLSTATE 42501 if a function with this name exists but the authorization ID of the statement does not have EXECUTE privilege, or EXECUTEIN privilege, or DATAACCESS authority on the schema of the function.

SPECIFIC specific-name

Identifies the particular user-defined function that is to be used as the source, by the specific-name either specified or defaulted to at function creation time. This syntax variant is not valid for a source function that is a built-in function.

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 or EXECUTEIN privilege or DATAACCESS authority on the schema 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 or EXECUTEIN privilege or schema DATAACCESS authority on the schema, an error (SQLSTATE 42501) is returned.

function-name (data-type,...)

Provides the function signature, which uniquely identifies the source function. This is the only valid syntax variant for a source function that is a built-in function.

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.

function-name

Gives the function name of the source function. If an unqualified name is provided, then the schemas of the user's SQL path are considered.

data-type

Must match the data type that was specified on the CREATE FUNCTION statement in the corresponding position (comma separated).

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.

PARAMETER CCSID

Specifies the encoding scheme to use for all string data passed into and out of the function. If the PARAMETER CCSID clause is not specified, the default is PARAMETER CCSID UNICODE for Unicode databases, and PARAMETER CCSID ASCII for all other databases.

ASCII

Specifies that string data is encoded in the database code page. If the database is a Unicode database, PARAMETER CCSID ASCII cannot be specified (SQLSTATE 56031). When the function is invoked, the application code page for the function is the database code page.

UNICODE

Specifies that string data is encoded in Unicode. If the database is a Unicode database, character data is in UTF-8, and graphic data is in UCS-2. If the database is not a Unicode database, character data is in UTF-8. In either case, when the function is invoked, the application code page for the function is 1208.

The PARAMETER CCSID clause must specify the same encoding scheme as the source function (SQLSTATE 53090).

AS TEMPLATE

Indicates that this statement will be used to create a function template, not a function with executable code.

NOT DETERMINISTIC or DETERMINISTIC

Specifies whether the function returns the same results for identical input arguments. The default is NOT DETERMINISTIC.

NOT DETERMINISTIC

Specifies that the function might not return the same result each time that the function is invoked with the same input arguments. The function depends on some state values that affect the results. The database manager uses this information during optimization of SQL statements. An example of a function that is not deterministic is one that generates random numbers.

A function that is not deterministic might receive incorrect results if it is executed by parallel tasks.

DETERMINISTIC

Specifies that the function always returns the same result each time that the function is invoked with the same input arguments. The database manager uses this information during optimization of SQL statements. An example of a function that is deterministic is one that calculates the square root of the input argument.

EXTERNAL ACTION or NO EXTERNAL ACTION

Specifies whether the function takes an action that changes the state of an object that the database manager does not manage. An example of an external action is sending a message or writing a record to a file. The default is EXTERNAL ACTION.

EXTERNAL ACTION

Specifies that the function takes an action that changes the state of an object that the database manager does not manage. EXTERNAL ACTION must be implicitly or explicitly specified if the SQL routine body invokes a function that is defined with EXTERNAL ACTION (SQLSTATE 428C2).

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.

NO EXTERNAL ACTION

Specifies that the function does not take any action that changes the state of an object that the database manager does not manage. The database manager uses this information during optimization of SQL statements.

Rules

• For convenience, in this section the function being created will be called CF and the function identified in the SOURCE clause will be called SF, no matter which of the three allowable syntaxes was used to identify SF.
  • The unqualified name of CF and the unqualified name of SF can be different.
  • A function named as the source of another function can, itself, use another function as its source. Extreme care should be exercised when exploiting this facility, because it could be very difficult to debug an application if an indirectly invoked function returns an error.
  • The following clauses are invalid if specified in conjunction with the SOURCE clause (because CF will inherit these attributes from SF):
    • CAST FROM ...,
    • EXTERNAL ...,
    • LANGUAGE ...,
    • PARAMETER STYLE ...,
    • DETERMINISTIC / NOT DETERMINISTIC,
    • FENCED / NOT FENCED,
    • RETURNS NULL ON NULL INPUT / CALLED ON NULL INPUT
    • EXTERNAL ACTION / NO EXTERNAL ACTION
    • NO SQL / CONTAINS SQL / READS SQL DATA
    • SCRATCHPAD / NO SCRATCHPAD
    • FINAL CALL / NO FINAL CALL
    • RETURNS TABLE (...)
    • CARDINALITY ...
    • ALLOW PARALLEL / DISALLOW PARALLEL
    • DBINFO / NO DBINFO
    • THREADSAFE / NOT THREADSAFE
    • INHERIT SPECIAL REGISTERS

  An error (SQLSTATE 42613) will result from violation of these rules.

• The number of input parameters in CF must be the same as those in SF; otherwise an error (SQLSTATE 42624) is returned.
• It is not necessary for CF to specify length, precision, or scale for a parameterized data type in the case of:
  • The function's input parameters,
  • Its RETURNS parameter

  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.

• The specification of the input parameters of CF are checked against those of SF. The data type of each parameter of CF must either be the same as or be castable to the data type of the corresponding parameter of SF. If any parameter is not the same type or castable, an error (SQLSTATE 42879) is returned.

  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.

• The specifications for the RETURNS data type of CF are checked against that of SF. The final RETURNS data type of SF, after any casting, must either be the same as or castable to the RETURNS data type of CF. Otherwise an error (SQLSTATE 42866) is returned.

  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.

• Revalidation of CF that does not have a parameter with a default expression is not supported (SQLSTATE 42997).

Notes

• Determining whether one data type is castable to another data type does not consider length or precision and scale for parameterized data types such as CHAR and DECIMAL. Therefore, errors may occur when using a function as a result of attempting to cast a value of the source data type to a value of the target data type. For example, VARCHAR is castable to DATE but if the source type is actually defined as VARCHAR(5), an error will occur when using the function.
• When choosing the data types for the parameters of a user-defined function, consider the rules for promotion that will affect its input values (see Promotion of data types). For example, a constant which may be used as an input value could have a built-in data type different from the one expected and, more significantly, may not be promoted to the data type expected. Based on the rules for promotion, it is generally recommended to use the following data types for parameters:
  • INTEGER instead of SMALLINT
  • DOUBLE instead of REAL
  • VARCHAR instead of CHAR
  • VARGRAPHIC instead of GRAPHIC
• Creating a function with a schema name that does not already exist will result in the implicit creation of that schema provided the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.
• For a federated server to recognize a data source function, the function must map to a counterpart at the federated database. If the database contains no counterpart, the user must create the counterpart and then the mapping.

  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.

• Privileges: The definer of a function always receives the EXECUTE privilege on the function, as well as the right to drop the function. The definer of the function is also given the WITH GRANT OPTION if any of the following conditions apply:
  • The source function is a built-in function.
  • The definer of the function has EXECUTE WITH GRANT OPTION on the source function.
  • The definer of the function has EXECUTEIN WITH GRANT OPTION on the schema containing the source function.
  • The function is a template.
• EXTERNAL ACTION functions: If an EXTERNAL ACTION function is invoked in other than the outermost select list, the results are unpredictable since the number of times the function is invoked will vary depending on the access plan used.
• Setting of the default value: Parameters of a function that are defined with a default value are set to their default value when the functions is invoked, but only if a value is not supplied for the corresponding argument, or is specified as DEFAULT, when the function is invoked.
• Create function mapping to table or row functions: A create function mapping to remote functions that returns a table or a row is not supported in a federated database.
• Inheriting SECURED or NOT SECURED attributes from the source function: The sourced user-defined function inherits the SECURED or NOT SECURED attribute from the source function in which only the topmost user-defined function is considered. If the topmost user-defined function is secure, any nested user-defined functions are considered secure. The database manager does not validate whether those nested user-defined functions are secure. If those nested functions can access sensitive data, the user with SECADM authority needs to ensure those functions are allowed to access those data and that a change control audit procedure has been established for all changes to those functions.

Examples

• Example 1:  Some time after the creation of Pellow's original CENTRE external scalar function, another user wants to create a function based on it, except this function is intended to accept only integer arguments.

     CREATE FUNCTION MYCENTRE (INTEGER, INTEGER)
       RETURNS FLOAT
       SOURCE PELLOW.CENTRE (INTEGER, FLOAT)

• Example 2:  A distinct type, HATSIZE, has been created based on the built-in INTEGER data type. It would be useful to have an AVG function to compute the average hat size of different departments. This is easily done as follows:

     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.
• Example 3:  In a federated system, a user wants to invoke an Oracle UDF that returns table statistics in the form of values with double-precision floating points. The federated server can recognize this function only if there is a mapping between the function and a federated database counterpart. But no such counterpart exists. The user decides to provide one in the form of a function template, and to assign this template to a schema called NOVA. The user uses the following code to register the template with the federated server.

     CREATE FUNCTION NOVA.STATS (DOUBLE, DOUBLE)
       RETURNS DOUBLE
       AS TEMPLATE DETERMINISTIC NO EXTERNAL ACTION

• Example 4:  In a federated system, a user wants to invoke an Oracle UDF that returns the dollar amounts that employees of a particular organization earn as bonuses. The federated server can recognize this function only if there is a mapping between the function and a federated database counterpart. No such counterpart exists; thus, the user creates one in the form of a function template. The user uses the following code to register this template with the federated server.

     CREATE FUNCTION BONUS ()
       RETURNS DECIMAL (8,2)
       AS TEMPLATE DETERMINISTIC NO EXTERNAL ACTION