DB2 10.5 for Linux, UNIX, and Windows

CREATE FUNCTION (sourced or template) statement

The CREATE FUNCTION (Sourced or Template) statement is used to register a function or function template with a server.

This statement can register the following objects:
  • A user-defined function, based on another existing scalar or aggregate function, at the current server.
  • A function template with an application server that is designated as a federated server. A function template is a partial function that contains no executable code. The user creates it for the purpose of mapping it to a data source function. After the mapping is created, the user can specify the function template in queries submitted to the federated server. When such a query is processed, the federated server will invoke the data source function to which the template is mapped, and return values whose data types correspond to those in the RETURNS portion of the template's definition.

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 include at least one of the following authorities:
  • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the function does not exist
  • CREATEIN privilege on the schema, if the schema name of the function exists
  • DBADM authority

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.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-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--)-'   

Notes:
  1. 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).
  2. DB2SECURITYLABEL is the built-in distinct type that must be used to define the row security label column of a protected table.
  3. 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 argument is specified and the first argument is a datetime data type
  • CHARACTER_LENGTH
  • COALESCE
  • CONTAINS
  • CURSOR_ROWCOUNT
  • DATAPARTITIONNUM
  • DBPARTITIONNUM
  • DEREF
  • EXTRACT
  • GRAPHIC when more than one argument is specified and the first argument is a datetime data type
  • GREATEST
  • HASHEDVALUE
  • INSERT when more than four arguments are specified
  • INSTR when more than four arguments are specified
  • LCASE when more than three arguments are specified
  • LEAST
  • LEFT when more than two arguments are specified
  • LENGTH when more than one argument is specified
  • LOCATE when more than three arguments are specified
  • LOCATE_IN_STRING when more than four arguments are specified
  • LOWER when more than three arguments are specified
  • MAX
  • MAX_CARDINALITY
  • MIN
  • NODENUMBER
  • NULLIF
  • NVL
  • OVERLAY
  • PARAMETER
  • POSITION
  • RAISE_ERROR
  • REC2XML
  • RID
  • RID_BIT
  • RIGHT when more than two arguments are specified
  • SCORE
  • STRIP
  • SUBSTRING
  • TRIM
  • TRIM_ARRAY
  • TYPE_ID
  • TYPE_NAME
  • TYPE_SCHEMA
  • UCASE when more than three arguments are specified
  • UPPER when more than three arguments are specified
  • VALUE
  • VARCHAR when more than one argument is specified and the first argument is a datetime data type
  • VARGRAPHIC when more than one argument is specified and the first argument 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 particular function that is to be used as the source and is valid only if there is exactly one specific function in the schema with this function-name for which the authorization ID of the statement has EXECUTE privilege. This syntax variant is not valid for a source function that is a built-in function.

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.

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

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

Notes

Examples