CREATE FUNCTION (SQL Scalar)

This statement is used to define a user-defined SQL scalar function. A scalar function returns a single value each time it is invoked. Specifying a function is generally valid wherever an SQL expression is valid.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

The privilege set defined below must include at least one of the following:

  • The CREATEIN privilege for the schema or all schemas
  • SYSADM or SYSCTRL authority

The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.

Syntax

CREATE FUNCTION function-name ([parameter-declaration[,...]])
RETURNS data-type2 
[LANGUAGE SQL] option-list RETURN-statement

parameter-declaration: 

[parameter-name] parameter-type

parameter-type: 

{data-type [AS LOCATOR] | TABLE LIKE {table-name | view-name} [AS LOCATOR]}

data-type: 

{built-in-data-type | distinct-type-name}

built-in-data-type: 

SMALLINT |
{INTEGER | INT} |
{DECIMAL | DEC | NUMERIC} [integer[,...]] |
{FLOAT [integer] | REAL [PRECISION] | DOUBLE} |
{ {CHARACTER | CHAR} [(integer)] | {CHARACTER | CHAR} VARYING (integer)
| VARCHAR (integer) } 
[FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | ASCII}] |
{{CHARACTER | CHAR} LARGE OBJECT | CLOB} [(integer [K|M|G])] } 
[FOR {SBCS | MIXED} DATA] [CCSID {EBCDIC | ASCII}] } |
{BINARY LARGE OBJECT | BLOB} (integer [K|M|G]) |
{GRAPHIC [(integer)] | VARGRAPHIC (integer) | DBCLOB (integer [K|M|G])} [CCSID {EBCDIC | ASCII}] |
{DATE | TIME | TIMESTAMP} |
ROWID

Description

function-name

Names the user-defined function. The name is implicitly or explicitly qualified by a schema name. The combination of name, schema name, the number of parameters, and the data type each parameter (without regard for any length, precision, scale, subtype, or encoding scheme attributes of the data type) must not identify a user-defined function that exists at the current server.

If the function is sourced on an existing function to enable the use of the existing function with a distinct type, the name can be the same name as the existing function. In general, more than one function can have the same name if the function signature of each function is unique.

  • The unqualified form of function-name is an SQL identifier.

    The name must not be any of the following system-reserved keywords even if you specify them as delimited identifiers:

    ALL LIKE UNIQUE
    AND MATCH UNKNOWN
    ANY NOT =
    BETWEEN NULL ¬=
    DISTINCT ONLY <
    EXCEPT OR <=
    EXISTS OVERLAPS ¬<
    FALSE SIMILAR >
    FOR SOME >=
    FROM TABLE ¬>
    IN TRUE <>
    IS TYPE  

    The unqualified function name is implicitly qualified with a schema name according to the following rules:

    • If the statement is embedded in a program, the schema name is the authorization ID in the QUALIFIER bind option when the plan or package was created or last rebound. If QUALIFIER was not specified, the schema name is the owner of the plan or package.
    • If the statement is dynamically prepared, the schema name is the SQL authorization ID in the CURRENT SQLID special register.
  • The qualified form of function-name is an SQL identifier (the schema name) followed by a period and an SQL identifier.

    The schema name can be "SYSTOOLS" if the user who executes the CREATE statement has SYSADM or SYSCTRL privilege. Otherwise, the schema name must not begin with "SYS" unless the schema name is "SYSADM".

The owner of the function is determined by how the CREATE FUNCTION statement is invoked:

  • If the statement is embedded in a program, the owner is the authorization ID of the owner of the plan or package.
  • If the statement is dynamically prepared, the owner is the SQL authorization ID in the CURRENT SQLID special register.

The owner is implicitly given the EXECUTE privilege with the GRANT option for the function.

(parameter-declaration,...)

Specifies the number of input parameters of the function and the data type of each parameter. All the parameters for a function are input parameters and are nullable. There must be one entry in the list for each parameter that the function expects to receive. Although not required, you can give each parameter a name.

A function can have no parameters. In this case, you must code an empty set of parentheses, for example:

CREATE FUNCTION WOOFER()

parameter-name

Specifies the name of the input parameter. The name is an SQL identifier, and each name in the parameter list must not be the same as any other name.

data-type

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

built-in-type

The data type of the input parameter is a built-in data type.

You can use the same built-in data types as for the CREATE TABLE statement.

For parameters with a character or graphic data type, the PARAMETER CCSID clause or CCSID clause indicates the encoding scheme of the parameter. If you do not specify either of these clauses, the encoding scheme is the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.

distinct-type-name

The data type of the input parameter is a distinct type. Any length, precision, scale, subtype, or encoding scheme attributes for the parameter are those of the source type of the distinct type.

The implicitly or explicitly specified encoding scheme of all the parameters with a string data type must be the same - either all ASCII, all EBCDIC, or all UNICODE.

Although parameters with a character data type have an implicitly or explicitly specified subtype (BIT, SBCS, or MIXED), the function program can receive character data of any subtype. Therefore, conversion of the input data to the subtype of the parameter might occur when the function is invoked.

Parameters with a datetime data type or a distinct type are passed to the function as a different data type:

  • A datetime type parameter is passed as a character data type, and the data is passed in ISO format.

    The encoding scheme for a datetime type parameter is the same as the implicitly or explicitly specified encoding scheme of any character or graphic string parameters. If no character or graphic string parameters are passed, the encoding scheme is the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.

  • A distinct type parameter is passed as the source type of the distinct type.

You can specify any built-in data type or distinct type that matches or can be cast to the data type of the corresponding parameter of the source function (the function that is identified in the SOURCE clause). Length, precision, or scale attributes do not have be specified for data types with these attributes. When specifying data types with these attributes, follow these rules:

  • An empty set of parentheses can be used to indicate that the length, precision, or scale is the same as the source function.
  • If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default values are used.

AS LOCATOR

Specifies that a locator to the value of the parameter is passed to the function instead of the actual value. Specify AS LOCATOR only for parameters with a LOB data type or a distinct type based on a LOB data type. Passing locators instead of values can result in fewer bytes being passed to the function, especially when the value of the parameter is very large.

The AS LOCATOR clause has no effect on determining whether data types can be promoted, nor does it affect the function signature, which is used in function resolution.

The XDB operating environment does not support the LOB data type.

TABLE LIKE table-name or view-name AS LOCATOR

Specifies that the parameter is a transition table. However, when the function is invoked, the actual values in the transition table are not passed to the function. A single value is passed instead. This single value is a locator to the table, which