CREATE FUNCTION (Sourced)

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

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

• The privilege to create in the schema. For more information, see Privileges necessary to create in a schema.
• Administrative authority

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

• For the SYSFUNCS catalog view and SYSPARMS catalog table:
  • The INSERT privilege on the table, and
  • The system authority *EXECUTE on library QSYS2
• Administrative authority

If the source function is a user-defined function, the authorization ID of the statement must include at least one of the following for the source function:

• The EXECUTE privilege on the function
• Administrative authority

To create a sourced function, the privileges held by the authorization ID of the statement must also include at least one of the following:

• The following system authorities:
  • *USE to the Create Service Program (CRTSRVPGM) command or
  • *USE to the Create Program (CRTPGM) command
• Administrative authority

If SQL names are specified and a user profile exists that has the same name as the library into which the function is created, and that name is different from the authorization ID of the statement, then the privileges held by the authorization ID of the statement must include at least one of the following:

• The system authority *ADD to the user profile with that name
• Administrative authority

If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:

• For each distinct type identified in the statement:
  • The USAGE privilege on the distinct type, and
  • The system authority *EXECUTE on the library containing the distinct type
• Administrative authority

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View, Corresponding System Authorities When Checking Privileges to a Function or Procedure, and Corresponding System Authorities When Checking Privileges to a Distinct Type.

Description

function-name

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

For SQL naming, the function will be created in the schema specified by the implicit or explicit qualifier.

For system naming, the function will be created in schema that is specified by the qualifier. If no qualifier is specified:

• If the value of the CURRENT SCHEMA special register is *LIBL, the function will be created in the current library (*CURLIB).
• Otherwise, the function will be created in the current schema.

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.

Certain function names are reserved for system use. For more information see Choosing the Schema and Function Name.

(parameter-declaration,...)

Specifies the number of input parameters of the function and the data type of each parameter. Each parameter-declaration specifies an input parameter for the function. A maximum of 90 parameters can be specified. A function can have zero or more input parameters. There must be one entry in the list for each parameter that the function expects to receive. All the parameters for a function are input parameters and are nullable. In the case of JAVA, numeric parameters other than the DECIMAL and NUMERIC types are not nullable. A runtime error will occur if a null value is input to such a parameter for a CALLED ON NULL INPUT function. For more information, see Defining the parameters.

parameter-name

Names the parameter. Although not required, a parameter name can be specified for each parameter. The name cannot be the same as any other parameter-name in the parameter list.

data-type1

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

Any valid SQL data type may be used, provided that 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 more information, see Considerations for invoking a sourced user-defined function.

built-in-type

The data type of the input parameter is a built-in data type. See CREATE TABLE for a more complete description of each built-in data type.

distinct-type-name

The data type of the input parameter is a 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). See CREATE TYPE (Distinct) for more information.

If the name of the distinct type is specified without a schema name, the database manager resolves the schema name by searching the schemas in the SQL path.

DataLinks are not allowed for functions sourced on external functions.

If a CCSID is specified, the parameter is converted to that CCSID prior to passing it to the function. If a CCSID is not specified, the CCSID is determined by the default CCSID at the current server at the time the function is invoked.

AS LOCATOR

Specifies that the input parameter is a locator to the value rather than the actual value. You can specify AS LOCATOR only if the input parameter has a LOB data type or a distinct type based on a LOB data type. If AS LOCATOR is specified, FOR SBCS DATA or FOR MIXED DATA must not be specified.

For more information on the AS LOCATOR clause, see Specifying AS LOCATOR for a parameter.

RETURNS

Specifies the result of the function.

data-type2

Specifies the data type of the column. The column is nullable. The data type can be a built-in data type (except LONG VARCHAR, LONG VARGRAPHIC, or a DataLink) or distinct type (that is not based on a DataLink).

Any valid SQL data type can be used provided it is castable from the result type of the source function. (For information about casting data types, see Casting between data types) However, this checking does not guarantee that an error will not occur when this new function is invoked. For more information, see Considerations for invoking a sourced user-defined function.

AS LOCATOR

Specifies that the function returns a locator to the value rather than the actual value. You can specify AS LOCATOR only if the output from the function has a LOB data type or a distinct type based on a LOB data type. If AS LOCATOR is specified, FOR SBCS DATA or FOR MIXED DATA must not be specified. The AS LOCATOR clause is not allowed for functions sourced on SQL functions.

For more information on the AS LOCATOR clause, see Specifying AS LOCATOR for a parameter.

SPECIFIC qualified-specific-name

Specifies a unique name for the function. For more information on specific names, see Specifying a specific name for a function.

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 shown, only the indicated form cannot be specified.):

• ARRAY_AGG
• BINARY when more than one argument is specified
• BLOB when more than one argument is specified
• CARDINALITY
• CHAR when more than one argument is specified
• CLOB when more than one argument is specified
• COALESCE
• CONTAINS
• DATAPARTITIONNAME
• DATAPARTITIONNUM
• DBCLOB when more than one argument is specified
• DBPARTITIONNAME
• DBPARTITIONNUM
• DECFLOAT when more than one argument is specified
• DECIMAL when more than one argument is specified
• DECRYPT_BIN when more than one argument is specified
• DECRYPT_BINARY when more than one argument is specified
• DECRYPT_BIT when more than one argument is specified
• DECRYPT_CHAR when more than one argument is specified
• DECRYPT_DB when more than one argument is specified
• EXTRACT
• GRAPHIC when more than one argument is specified
• HASH
• HASHED_VALUE
• LAND
• LOR
• MAX
• MAX_CARDINALITY
• MIN
• NODENAME
• NODENUMBER
• PARTITION
• POSITION
• RAISE_ERROR
• RID
• RRN
• SCORE
• STRIP
• SUBSTRING
• TRANSLATE when more than one argument is specified
• TRIM
• TRIM_ARRAY
• VALUE
• VARBINARY when more than one argument is specified
• VARCHAR when more than one argument is specified
• VARGRAPHIC when more than one argument is specified
• XMLAGG
• XMLATTRIBUTES
• XMLCOMMENT
• XMLCONCAT
• XMLDOCUMENT
• XMLELEMENT
• XMLFOREST
• XMLGROUP
• XMLNAMESPACES
• XMLPARSE
• XMLPI
• XMLROW
• XMLSERIALIZE
• XMLTEXT
• XMLVALIDATE
• XOR
• XSLTRANSFORM
• ZONED when more than one argument is specified

function-name

Identifies the function to be used as the source function by its function name. The source function can be defined with any number of parameters. If more than one function is defined with the specified name in the specified or implicit schema, an error is returned.

If an unqualified function-name is specified, the SQL path is used to locate the function. The database manager selects the first schema that has only one function with this name on which the user has EXECUTE authority. An error is returned if a function is not found, or if the database manager encounters a schema that has more than one function with this name.

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

Identifies the function to be used as the source function by its function signature, which uniquely identifies the function. The function-name (parameter-type,...) must identify a function with the specified signature at the current server. The specified parameters must match the data types in the corresponding position that were specified when the function was created. The number of data types, and the logical concatenation of the data types is used to identify the specific function instance. Synonyms for data types are considered a match.

If function-name() is specified, the function identified must have zero parameters.

To use a built-in function as the source function, this syntax variation must be used.

function-name

Identifies the name of the source function. If an unqualified name is specified, the schemas of the SQL path are searched. Otherwise, the database manager searches for the function in the specified schema.

parameter-type,...

Identifies the parameters of the function.

If an unqualified distinct type name is specified, the database manager searches the SQL path to resolve the schema name for the distinct type.

Empty parentheses are allowed for some data types that are specified in this context. For data types that have a length, precision or scale attribute, use one of the following specifications:

• Empty parentheses indicate that the database manager ignores the attribute when determining whether the data types match. For example, DEC() will be considered a match for a parameter of a function defined with a data type of DEC(7,2).
• If a specific value for a length, precision, or scale attribute is specified, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement. If the data type is FLOAT, the precision does not have to exactly match the value that was specified because matching is based on the data type (REAL or DOUBLE).
• If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.

For data types with a subtype or CCSID attribute, specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that the database manager ignores the attribute when determining whether the data types match. If you specify either clause, it must match the value that was implicitly or explicitly specified in the CREATE FUNCTION statement.

AS LOCATOR

Specifies that the function is defined to receive a locator for this parameter. If AS LOCATOR is specified, the data type must be a LOB or a distinct type based on a LOB. If AS LOCATOR is specified, FOR SBCS DATA or FOR MIXED DATA must not be specified. If AS LOCATOR is specified and a length is explicitly specified, the data type length is ignored.

For more information on the AS LOCATOR clause, see Specifying AS LOCATOR for a parameter.

SPECIFIC qualified-specific-name

Identifies the function to be used as the source function by its specific name. The qualified-specific-name must identify a specific function that exists in the specified schema.

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.

If a CCSID is specified and the CCSID of the return data is encoded in a different CCSID, the data is converted to the specified CCSID.

If a CCSID is not specified the return data is converted to the CCSID of the job (or associated graphic CCSID of the job for graphic string return values), if the CCSID of the return data is encoded in a different CCSID. To avoid any potential loss of characters during the conversion, consider explicitly specifying a CCSID that can represent any characters that will be returned from the function. This is especially important if the data type is graphic string data. In this case, consider using CCSID 1200 or 13488 (Unicode graphic string data).

Notes

General considerations for defining user-defined functions: See CREATE FUNCTION for general information about defining user-defined functions.

Function ownership: If SQL names were specified:

• If a user profile with the same name as the schema into which the function is created exists, the owner of the function is that user profile.
• Otherwise, the owner of the function is the user profile or group user profile of the job executing the statement.

If system names were specified, the owner of the function is the user profile or group user profile of the job executing the statement.

Function authority: If SQL names are used, functions are created with the system authority of *EXCLUDE on *PUBLIC. If system names are used, functions are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.

If the owner of the function is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the function.

Considerations for invoking a sourced user-defined function: When a sourced function is invoked, each argument to the function is assigned to the associated parameter defined for the function. The values are then cast (if necessary) to the data type of the corresponding parameters of the underlying function. An error can occur either in the assignment or in the cast. For example: an argument passed on input to a function that matches the data type and length or precision attributes of the parameter for the function might not be castable if the corresponding parameter of the underlying source function has a shorter length or less precision. It is recommended that the data types of the parameters of a sourced function be defined with attributes that are less than or equal to the attributes of the corresponding parameters of the underlying function.

The result of the underlying function is assigned to the RETURNS data type of the sourced function. The RETURNS data type of the underlying function might not be castable to the RETURNS data type of the source function. This can occur when the RETURNS data type of this new source function has a shorter length or less precision than the RETURNS data type of the underlying function. For example, an error would occur when function A is invoked assuming the following functions exist. Function A returns an INTEGER. Function B is a sourced function, is defined to return a SMALLINT, and the definition references function A in the SOURCE clause. It is recommended that the RETURNS data type of a sourced function be defined with attributes that are the same or greater than the attributes defined for the RETURNS data type of the underlying function.

Considerations when the function is based on a user-defined function: If the sourced function is based 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 for function C.

Creating the function: When a sourced function is created, a small service program object is created that represents the function. When this service program is saved and restored to another system, the attributes from the CREATE FUNCTION statement are automatically added to the catalog on that system.