CREATE FUNCTION (sourced)

Invocation

This statement can be embedded in an application program or issued interactively.

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 [AS LOCATOR] [SPECIFIC specific-name]
SOURCE {function-name | SPECIFIC specific-name | function-name (parameter-type [,...])}

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

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 of 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 a long SQL identifier. The name must not be any of the following system-reserved keywords even if you specify them as delimited identifiers:

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 a short SQL identifier (the schema name) followed by a period and a long SQL identifier.

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

Identifies the number of input parameters of the function, and specifies the data type of each parameter. All the parameters for a function are input parameters. 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 a long 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.

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.
• 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 length of the data type is implied. For example:

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 at this time.

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 the function uses to access the columns of the transition table. A function with a table parameter can only be invoked from the triggered action of a trigger.

The use of TABLE LIKE provides an implicit definition of the transition table. It specifies that the transition table has the same number of columns as the identified table or view. The columns have the same data type, length, precision, scale, subtype, and encoding scheme as the identified table or view, as they are described in catalog tables SYSCOLUMNS and SYSTABLESPACES.

The name specified after TABLE LIKE must identify a table or view that exists at the current server. The name must not identify a declared temporary table. The name does not have to be the same name as the table that is associated with the transition table for the trigger. An unqualified table or view name is implicitly qualified according to the following rules:

• If the CREATE FUNCTION statement is embedded in a program, the implicit qualifier is the authorization ID in the QUALIFIER bind option when the plan or package was created or last rebound. If QUALIFIER was not used, the implicit qualifier is the owner of the plan or package.
• If the CREATE FUNCTION statement is dynamically prepared, the implicit qualifier is the SQL authorization ID in the CURRENT SQLID special register.

When the function is invoked, the corresponding columns of the transition table identified by the table locator and the table or view identified in the TABLE LIKE clause must have the same definition. The data type, length, precision, scale, and encoding scheme of these columns must match exactly. The description of the table or view at the time the CREATE FUNCTION statement was executed is used.

XDB Server operating environment does not support transition table parameters at this time.

RETURNS

Identifies the output of the function.

SPECIFIC specific-name

Specifies a unique name for the function. The name is implicitly or explicitly qualified with a schema name. The name, including the schema name, must not identify the specific name of another function that exists at the current server.

The unqualified form of specific-name is a long SQL identifier. The qualified form is a short SQL identifier (the schema name) followed by a period and a long SQL identifier.

If you do not specify a schema name, it is the same as the explicit or implicit schema name of the function name (function-name). If you specify a schema name, it must be the same as the explicit or implicit schema name of the function name.

If you do not specify the SPECIFIC clause, the default specific name is the name of the function. However, if the function name does not provide a unique specific name or if the function name is a single asterisk , XDB generates a specific name in the form of:

SQLxxxxxxxxxxxx

where xxxxxxxxxxxx is a string of 12 characters that make the name unique.

The specific name is stored in the SPECIFIC column of the SYSROUTINES catalog table. The specific name can be used to uniquely identify the function in several SQL statements (such as ALTER FUNCTION, COMMENT ON, DROP, GRANT, and REVOKE). However, the function cannot be invoked by its specific name.

SOURCE

Specifies that the function that you are registering is a sourced function. A sourced function is implemented by another function (the source function). The source function can be any previously created user-defined function except an external table function. The source function can also be any built-in function except the following (if a particular syntax is shown, only the indicated form cannot be specified):

• COUNT(*)
• COUNT_BIG(*)
• CHAR(datetime-expression, second-argument) where second-argument is ISO, USA, EUR, JIS, or LOCAL
• COALESCE
• NULLIF
• RAISE-ERROR
• STRIP with any optional parameters specified
• VALUE

If you base the sourced function directly or indirectly on an external scalar function, the sourced function inherits the attributes of the external scalar function. This can involve several layers of sourced functions. For example, assume that function A is sourced on function B, which in turn is sourced on function C. Function C is an external scalar function. Functions A and B inherit all of the attributes that are specified on the EXTERNAL clause of the CREATE FUNCTION statement for function C.

EXECUTE authority is required on the source function.

To specify a built-in function as the source function, use the last syntax variation in the following list, function-name (parameter-type, ...).

function-name

Identifies the function to be used as the source function by its function name. A schema name implicitly or explicitly qualifies the name. Only one function with this name must exist in the schema.

If you specify an unqualified function-name, XDB uses the SQL path of the authorization ID (the value of the CURRENT PATH special register) to locate the function. The first schema that has only one function with this name on which the user has EXECUTE authority is selected. XDB returns an error if it cannot find a function or encounters a schema that has more than one function with this name.

If you specify a qualified function-name, XDB returns an error if there is no function with this name in the named schema or more than one function with this name exists in the schema.

SPECIFIC specific-name

Identifies the function to be used as the source function by its specific name. A schema name implicitly or explicitly qualifies the name.

If you specify an unqualified specific-name, XDB uses the SQL path of the authorization ID (the value of the CURRENT PATH special register) to locate the schema. XDB searches the SQL path and selects the first schema that contains a function with this specific name for which the user has EXECUTE authority. XDB returns an error if it cannot find a function with the specific name in one of the schemas in the SQL path.

If you specify a qualified specific-name, XDB searches the named schema for the function. XDB returns an error if it cannot find a function with the specific name.

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

Identifies the function to be used as the source function by its function signature. You must use this form of the syntax if the source function is a built-in function. You cannot use this form of the syntax if the source function was defined with a table parameter (the LIKE TABLE was specified in the CREATE FUNCTION statement to indicate that one of the input parameters is a transition table). Instead, identify the function with its function name, if unique, or with its specific name.

XDB does not use function resolution to select the source function because the function signature uniquely identifies the function.

For either empty parentheses or specific values, the synonyms for data types are considered a match. For example, DEC and NUMERIC will match.

If you omit the FOR DATA or CCSID clause for data types with a subtype or encoding scheme attribute, XDB ignores the attribute when determining whether the data types match.

If no function with the specified signature exists in the explicitly or implicitly specified schema, an error occurs.

The number of input parameters in the function that is being created must be the same as the number of parameters in the source function. If the data type of each input parameter is not the same as or castable to the corresponding parameter of the source function, an error occurs. The data type of the final result of the source function must match or be castable to the result of the sourced function.

Choosing data types for parameters

When you choose the data types of the input and output parameters for your function, consider the rules of promotion that can affect the values of the parameters. For example, a constant that is one of the input arguments to the function might have a built-in data type that is different from the data type that the function expects, and more significantly, might not be promotable to that expected data type. Based on the rules of promotion, using the following data types for parameters is recommended:

• INTEGER instead of SMALLINT
• DOUBLE instead of REAL
• VARCHAR instead of CHAR
• VARGRAPHIC instead of GRAPHIC

For portability of functions across platforms that are not DB2 for OS/390, do not use the following data types, which might have different representations on different platforms:

• FLOAT. Use DOUBLE or REAL instead.
• NUMERIC. Use DECIMAL instead.

Determining the uniqueness of functions in a schema

At the current server, the function signature of each function, which is the qualified function name combined with the number and data types of the input parameters, must be unique This means that two different schemas can each contain a function with the same name that have the same data types for all of their corresponding data types. However, a single schema must not contain multiple functions with the same name that have the same data types for all of their corresponding data types.

When determining whether corresponding data types match, XDB does not consider any length, precision, scale, subtype or encoding scheme attributes in the comparison. XDB considers the synonyms of data types (DECIMAL and NUMERIC, REAL and FLOAT, and DOUBLE and FLOAT) a match. Therefore, CHAR(8) and CHAR(35) are considered to be the same, as are DECIMAL(11,2), DECIMAL(4,3), and NUMERIC(4,2).

Assume that the following statements are executed to create four functions in the same schema. The second and fourth statements fail because they create functions that are duplicates of the functions that the first and third statements created.

CREATE FUNCTION PART (INT, CHAR(15)) ...
CREATE FUNCTION PART (INTEGER, CHAR(40)) ...
CREATE FUNCTION ANGLE (DECIMAL(12,2)) ...
CREATE FUNCTION ANGLE (DEC(10,7)) ...

Rules for creating sourced functions

For the discussion in this section, assume that the function that is being created is named NEWF and the source function is named SOURCEF. Consider the following rules when creating a sourced function:

• The unqualified names of the sourced function and source function can be different (NEWF and SOURCEF).
• The number of input parameters for NEWF and SOURCEF must be the same; otherwise, an error occurs when the CREATE FUNCTION statement is executed.
• When specifying the input parameters and output for NEWF, you can specify a value for the precision, scale, subtype, or encoding scheme for a data type with any of these attributes or use empty parentheses.

  Empty parentheses, such as VARCHAR(), indicate that the value of the attribute is the same as the attribute for the corresponding parameter of SOURCEF, or that is determined by data type promotion. If you specify any values for the attributes, XDB checks the values against the corresponding input parameters and returned output of SOURCEF as described next.

• When the CREATE FUNCTION statement is executed, XDB checks the input parameters of NEWF against those of SOURCEF. The data type of each input parameter of NEWF function must be either the same as, or promotable to, the data type of the corresponding parameter of SOURCEF; otherwise, an error occurs.

  This checking does not guarantee that an error will not occur when NEWF is invoked. For example, an argument that matches the data type and length or precision attributes of a NEWF parameter might not be promotable if the corresponding SOURCEF parameter has a shorter length or less precision. In general, do not define the parameters of a sourced function with length or precision attributes that are greater than the attributes of the corresponding parameters of the source function.

• When the CREATE FUNCTION statement is executed, XDB checks the data type identified in the RETURNS clause of NEWF against the data type that SOURCEF returns. The data type that SOURCEF returns must be either the same as, or promotable to, the RETURNS data type of NEWF; otherwise, an error occurs.

  This checking does not guarantee that an error will not occur when NEWF is invoked. For example, the value of a result that matches the data type and length or precision attributes of those specified for SOURCEF's result might not be promotable if the RETURNS data type of NEWF has a shorter length or less precision. Consider the possible effects of defining the RETURNS data type of a sourced function with length or precision attributes that are less than the attributes defined for the data type returned by source function.

Examples:

Assume that you created a distinct type HATSIZE, which you based on the built-in data type INTEGER. You want to have an AVG function to compute the average hat size of different departments. Create a sourced function that is based on built-in function AVG.

CREATE FUNCTION AVE (HATSIZE) RETURNS HATSIZE
    SOURCE SYSIBM.AVG (INTEGER);

When you created distinct type HATSIZE, two cast functions were generated, which allow HATSIZE to be cast to INTEGER for the argument and INTEGER to be cast to HATSIZE for the result of the function.

After Smith registered the external scalar function CENTER in his schema, you decide that you want to use this function, but you want it to accept two INTEGER arguments instead of one INTEGER argument and one FLOAT argument. Create a sourced function that is based on CENTER.

CREATE FUNCTION MYCENTER (INTEGER, INTEGER)
    RETURNS FLOAT
    SOURCE SMITH.CENTER (INTEGER, FLOAT);