SQL Reference

CREATE FUNCTION (SQL Scalar, Table or Row)

This statement is used to define a user-defined SQL scalar, table or row function. A scalar function returns a single value each time it is invoked, and is generally valid wherever an SQL expression is valid. A table function may be used in a FROM clause and returns a table. A row function may be used as a transform function and returns a row.

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

SYSADM or DBADM authority
IMPLICIT_SCHEMA authority on the database, if the schema name of the function does not refer to an existing schema
CREATEIN privilege on the schema, if the schema name of the function refers to an existing schema.

If the authorization ID of the statement does not have SYSADM or DBADM authority, and the function identifies a table or view, the privileges that the authorization ID of the statement holds (without considering GROUP privileges) must include SELECT WITH GRANT OPTION for each identified table and view.

If a function definer can only create the function because the definer has SYSADM authority, then the definer is granted implicit DBADM authority for the purpose of creating the function.

If the authorization ID has insufficient authority to perform the operation, an error (SQLSTATE 42502) is raised.

Syntax

>>-CREATE FUNCTION--function-name------------------------------->
 
>----(--+------------------------------------+---)---*---------->
        |  .-,----------------------------.  |
        |  V                              |  |
        '----parameter-name--data-type1---+--'
 
>----RETURNS--+-data-type2--------------------+--*-------------->
              '--+-ROW---+---| column-list |--'
                 '-TABLE-'
 
                                         .-LANGUAGE SQL--.
>-----+--------------------------+--*----+---------------+--*--->
      '-SPECIFIC--specific-name--'
 
      .-NOT DETERMINISTIC--.       .-EXTERNAL ACTION----.
>-----+--------------------+--*----+--------------------+--*---->
      '-DETERMINISTIC------'       '-NO EXTERNAL ACTION-'
 
      .-READS SQL DATA--.       .-STATIC DISPATCH--.
>-----+-----------------+--*----+------------------+--*--------->
      '-CONTAINS SQL----'
 
                              (1)
      .-CALLED ON NULL INPUT-------.
>-----+----------------------------+--*------------------------->
 
>-----+-----------------------------------------------------+--->
      |                                               (2)   |
      '-PREDICATES--(--| predicate-specification |--)-------'
 
>----RETURN--+-expression----------------------------------------------+>
             +-NULL----------------------------------------------------+
             '-+---------------------------------------+---fullselect--'
               |       .-,--------------------------.  |
               |       V                            |  |
               '-WITH-----common-table-expression---+--'
 
>--------------------------------------------------------------><
 
column-list
 
       .-,--------------------------.
       V                            |
|---(-----column-name--data-type3---+---)-----------------------|

Notes:

NULL CALL may be specified in place of CALLED ON NULL INPUT
Valid only if RETURNS specifies a scalar result (data-type2)

Description

function-name

Names the function being defined. It is a qualified or unqualified name that designates a function. The unqualified form of function-name is an SQL identifier (with a maximum length of 18). 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 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 may not 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 as described in Basic Predicate.

The same name can be used for more than one function if there is some difference in the signature of the functions. Although there is no prohibition against it, an external user-defined table function should not be given the same name as a built-in function.

parameter-name

A name that is distinct from the names of all other parameters in this function.

data-type1

Specifies the data type of the parameter:

SQL data type specifications and abbreviations that may be specified in the data-type1 definition of a CREATE TABLE statement.
REF may be specified, but that REF is unscoped. The system does not attempt to infer the scope of the parameter or result. Inside the body of the function, a reference type can be used in a dereference operation only by first casting it to have a scope. Similarly, a reference returned by an SQL function can be used in a dereference operation only by first casting it to have a scope.
LONG VARCHAR and LONG VARGRAPHIC data types may not be used (SQLSTATE 42815).

RETURNS

This mandatory clause identifies the type of output of the function.

data-type2

Specifies the data type of the output.

In this statement, exactly the same considerations apply as for the parameters of SQL functions described above under data-type1 for function parameters.

ROW column-list

Specifies that the output of the function is a single row. If the function returns more than one row, an error is raised (SQLSTATE 21505). The column-list must include at least two columns (SQLSTATE 428F0).

A row function can only be used as a transform function for a structured type (having one structured type as its parameter and returning only base types).

TABLE column-list

Specifies that the output of the function is a table.

column-list

The list of column names and data types returned for a ROW or TABLE function

column-name: Specifies the name of this column. The name cannot be qualified and the same name cannot be used for more than one column of the row.
data-type3: Specifies the data type of the column, and can be any data type supported by a parameter of the SQL function.

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 (with a maximum length of 18). 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 is raised (SQLSTATE 42710).

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 is raised (SQLSTATE 42882).

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.

LANGUAGE SQL

Specifies that the function is written using SQL. The supported SQL is currently limited to the RETURN statement.

DETERMINISTIC or NOT DETERMINISTIC

This optional clause specifies whether the function always returns the same results for given argument values (DETERMINISTIC) or whether the function depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC function must always return the same table from successive invocations with identical inputs. Optimizations taking advantage of the fact that identical inputs always produce the same results are prevented by specifying NOT DETERMINISTIC.

NOT DETERMINISTIC must be explicitly or implicitly specified if the body of the function accesses a special register or calls another non-deterministic function (SQLSTATE 428C2).

NO EXTERNAL ACTION or EXTERNAL ACTION

This optional clause specifies whether or not the function takes some action that changes the state of an object not managed by the database manager. By specifying NO EXTERNAL ACTION, the system can use certain optimizations that assume functions have no external impacts.

EXTERNAL ACTION must be explicitly or implicitly specified if the body of the function calls another function that has an external action (SQLSTATE 428C2).

READS SQL DATA or CONTAINS SQL

Indicates what type of SQL statements can be executed. Because the SQL statement supported is the RETURN statement, the distinction has to do with whether or not the expression is a subquery.

READS SQL DATA: Indicates that SQL statements that do not modify SQL data can be executed by the function (SQLSTATE 42985). Nicknames or OLEDB table functions cannot be referenced in the SQL statement (SQLSTATE 42997).
CONTAINS SQL: Indicates that SQL statements that neither read nor modify SQL data can be executed by the function (SQLSTATE 42985).

STATIC DISPATCH

This optional clause indicates that at function resolution time, DB2 chooses a function based on the static types (declared types) of the parameters of the function.

CALLED ON NULL INPUT

This clause indicates that the function is called regardless of whether any of its arguments are null. It can return a null value or a non-null value. Responsibility for testing null argument values lies with the user-defined function.

The phrase NULL CALL may be used in place of CALLED ON NULL INPUT.

PREDICATES

For predicates using this function, this clause identifies those that can exploit the index extensions, and can use the optional SELECTIVITY clause for the predicate's search condition. If the PREDICATES clause is specified, the function must be defined as DETERMINISTIC with NO EXTERNAL ACTION (SQLSTATE 42613).

predicate-specification: See CREATE FUNCTION (External Scalar) for details on predicate specifications.

RETURN

Specifies the return value of the function. Parameter names can be referenced in the RETURN statement. Parameter names may be qualified by the function name to avoid ambiguous references.

expression

Specifies the expression to be returned for the function. The result data type of the expression must be assignable (using store assignment rules) to the data type defined in the RETURNS clause (SQLSTATE 42866). A scalar expression (other than a scalar fullselect) cannot be specified for a table function (SQLSTATE 428F1).

NULL

Specifies that the function returns a null value of the data type defined in the RETURNS clause.

WITH common-table-expression

Defines a common table expression for use with the fullselect that follows. See common-table-expression.

fullselect

Specifies the row or rows to be returned for the function. The number of columns in the fullselect must match the number of columns in the function result (SQLSTATE 42811), and the static column types of the fullselect must be assignable to the declared column types of the function result, using the rules for assignment to columns (SQLSTATE 42866).

If the function is a scalar function, the fullselect must return one column (SQLSTATE 42823) and, at most, one row (SQLSTATE 21000).

If the function is a row function, it must return, at most, one row (SQLSTATE 21505).

If the function is a table function, it can return zero or more rows with one or more columns.

Notes

Resolution of function calls inside the function body is done according to the function path that is effective for the CREATE FUNCTION statement and does not change after the function is created.
If an SQL function contains multiple references to any of the date or time special registers, all references return the same value, and it will be the same value returned by the register invocation in the statement that called the function.
The body of an SQL function must not contain a recursive call to itself or to another function or method that calls it.
The following rules are enforced by all statements that create functions or methods:
- A function may not have the same signature as a method (comparing the first parameter-type of the function with the subject-type of the method).
- A function and a method may not be in an overriding relationship. That is, if the function were a method with its first parameter as subject, it must not override, or be overridden by, another method.
- Since overriding does not apply to functions, it is permissible for two functions to exist such that, if they were methods, one would override the other.
For the purpose of comparing parameter-types in the above rules:
- Parameter-names, lengths, AS LOCATOR, and FOR BIT DATA are ignored.
- A subtype is considered to be different from its supertype.

Examples

Example 1: Define a scalar function that returns the tangent of a value using the existing sine and cosine functions.

     CREATE FUNCTION TAN (X DOUBLE)
        RETURNS DOUBLE
        LANGUAGE SQL
        CONTAINS SQL
        NO EXTERNAL ACTION
        DETERMINISTIC
        RETURN SIN(X)/COS(X)

Example 2: Define a transform function for the structured type PERSON.

     CREATE FUNCTION FROMPERSON (P PERSON)
        RETURNS ROW (NAME VARCHAR(10), FIRSTNAME VARCHAR(10))
        LANGUAGE SQL
        CONTAINS SQL
        NO EXTERNAL ACTION
        DETERMINISTIC
        RETURN VALUES (P..NAME, P..FIRSTNAME)

Example 3: Define a table function that returns the employees in a specified department number.

     CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
        RETURNS TABLE (EMPNO CHAR(6),
                       LASTNAME VARCHAR(15),
                       FIRSTNAME VARCHAR(12))
        LANGUAGE SQL
        READS SQL DATA
        NO EXTERNAL ACTION
        DETERMINISTIC
        RETURN
           SELECT EMPNO, LASTNAME, FIRSTNME
             FROM EMPLOYEE
             WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO

Note that the definer of this function must have the SELECT WITH GRANT OPTION privilege on the EMPLOYEE table and that all users may invoke the table function DEPTEMPLOYEES, effectively giving them access to the data in the result columns for each department number.

[ Top of Page | Previous Page | Next Page ]