CREATE FUNCTION (SQL Table)
This CREATE FUNCTION (SQL table) statement creates an SQL table function at the current server. The function returns a single result table.
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
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
- 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
To replace an existing function, the privileges
held by the authorization ID of the statement must include at least
one of the following:
- The following system authorities:
- The system authority of *OBJMGT on the service program object associated with the function
- All authorities needed to DROP the function
- The system authority *READ to the SYSFUNCS catalog
view and SYSPARMS catalog table
- Administrative authority
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View and Corresponding System Authorities When Checking Privileges to a Distinct Type.
Syntax
>>-CREATE--+------------+--FUNCTION--function-name--------------> '-OR REPLACE-' >--(--+---------------------------+--)--------------------------> | .-,---------------------. | | V | | '---parameter-declaration-+-' >--+-function-definition----------------+---------------------->< '-WRAPPED--obfuscated-statement-text-' function-definition .-,-----------------------. V | |--RETURNS TABLE--(----column-name--data-type2-+--)-------------> >--option-list--+----------------------+--SQL-routine-body------| '-SET OPTION-statement-' parameter-declaration |--parameter-name--data-type1-----------------------------------| data-type1, data-type2 |--+-built-in-type------+---------------------------------------| '-distinct-type-name-'
- This clause and the clauses that follow in the option-list can be specified in any order. Each clause can be specified at most once.
Description
- OR REPLACE
- Specifies to replace the definition
for the function if one exists at the current server. The existing
definition is effectively dropped before the new definition is replaced
in the catalog with the exception that privileges that were granted
on the function are not affected. This option is ignored if a definition
for the function does not exist at the current server. To replace
an existing function, the specific-name and function-name of
the new definition must be the same as the specific-name and function-name of
the old definition, or the signature of the new definition must match
the signature of the old definition. Otherwise, a new function is
created.
- 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 the 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.
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 1024 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. For more information, see Defining the parameters.
- parameter-name
- Names the parameter. The name is used to refer to the parameter within the body of the function. The name cannot be the same as any other parameter-name in the parameter list.
- data-type1
- Specifies the data type of the input parameter. The data type
can be a built-in data type or a distinct data type.
- built-in-type
- Specifies a built-in data type. For a more complete description of each built-in data type, see CREATE TABLE.
- distinct-type-name
- Specifies 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). For more information about creating
a distinct type, see CREATE TYPE (Distinct).
If the name of the distinct type is unqualified, the database manager resolves the schema name by searching the schemas in the SQL path.
If a CCSID is specified, the parameter will be 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.
- RETURNS TABLE
- Specifies
the output table of the function.
Assume the number of parameters is N. There must be no more than
1025-N columns.- column-name
- Specifies the name of a column of the output table. Do not specify the same name more than once.
- data-type2
- Specifies the data type and attributes of the output.
You can specify any built-in data type (except LONG VARCHAR, or LONG VARGRAPHIC) or a distinct type. When the function is invoked the results are assigned to these data types (using storage assignment rules).
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
and
the function is not referenced in a view, 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).If a CCSID is not specified
and the function is referenced in a view, the return data is converted
to the CCSID of the associated view column. 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).
- LANGUAGE SQL
- Specifies that this is an SQL function.
- SPECIFIC specific-name
- Specifies a unique name for the function. For more information on specific names, see Specifying a specific name for a function.
- DETERMINISTIC or NOT DETERMINISTIC
- Specifies whether
the function returns the same results each time that the function
is invoked with the same 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 table function that is not deterministic is one that references special registers, non-deterministic functions, or a sequence in a way that affects the table function result table.
- DETERMINISTIC
- Specifies that the function always returns the same
result table each time that the function is invoked with the same
input arguments. The database manager uses this information during
optimization of SQL statements.1
- 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 stream file. The default
is EXTERNAL ACTION.
- EXTERNAL ACTION
- Specifies that the function can take an action that changes the state of an object that the database manager does not manage. Thus, the function must be invoked with each successive function invocation. EXTERNAL ACTION should be specified if the function contains a reference to another function that has an external action.
- NO EXTERNAL ACTION
- The function does not perform an external action. It need not
be called with each successive function invocation.
NO EXTERNAL ACTION functions might perform better than EXTERNAL ACTION functions because they might not be invoked for each successive function invocation.
- CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA
- Specifies
the classification of SQL statements that the function can execute.
The database manager verifies that the SQL statements that the function
issues are consistent with this specification. For the classification
of each statement, see Characteristics of SQL statements. The default is READS SQL DATA.
- READS SQL DATA
- Specifies that the function can execute statements with a data access classification of READS SQL DATA, CONTAINS SQL, or NO SQL. The function cannot execute SQL statements that modify data.
- CONTAINS SQL
- Specifies that the function can execute only SQL statements with a data access classification of CONTAINS SQL or NO SQL. The function cannot execute any SQL statements that read or modify data.
- MODIFIES SQL DATA
- The function can execute any SQL statement except those statements that are not supported in any function.
- RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT
- Specifies
whether the function is called if any of the input arguments is null
at execution time.
- RETURNS NULL ON NULL INPUT
- Specifies that the function is not called if any of the input arguments is null. The result is an empty table, which is a table with no rows. RETURNS NULL ON NULL INPUT is the default.
- CALLED ON NULL INPUT
- Specifies that the function is to be invoked if any argument values are null. This specification means that the function must be coded to test for null argument values. The function can return an empty table, depending on its logic.
- INHERIT SPECIAL REGISTERS
- Specifies that existing values of special registers are inherited upon entry to the function.
- STATIC DISPATCH
- Specifies that the function is dispatched statically. All functions are statically dispatched.
- FENCED or NOT FENCED
- Specifies
whether the SQL function runs in an environment that is isolated from
the database manager environment. FENCED is the default.
- FENCED
- The function will run in a separate thread.
FENCED functions cannot keep SQL cursors open across individual calls to the function. However, the cursors in one thread are independent of the cursors in any other threads which reduces the possibility of cursor name conflicts.
- NOT FENCED
- The function may run in the same thread as the invoking SQL statement.
NOT FENCED functions can keep SQL cursors open across individual calls to the function. Since cursors can be kept open, the cursor position will also be preserved between calls to the function. However, cursor names may conflict since the UDF is now running in the same thread as the invoking SQL statement and other NOT FENCED UDFs.
NOT FENCED functions usually perform better than FENCED functions.
- ALLOW PARALLEL or DISALLOW PARALLEL
- Specifies whether
the function can be run in parallel.
The default is DISALLOW PARALLEL if one or more of the following clauses are specified: NOT DETERMINISTIC, EXTERNAL ACTION, or MODIFIES SQL DATA,
or if this
is a pipelined table function. Otherwise, ALLOW PARALLEL is the
default.- ALLOW PARALLEL
- Specifies that the database manager can consider parallelism for
the function. The database manager is not required to use parallelism
on the SQL statement that invokes the function or on any SQL statement
issued from within the function.
See the descriptions of NOT DETERMINISTIC, EXTERNAL ACTION, and MODIFIES SQL DATA for considerations that apply to specification of ALLOW PARALLEL.
- DISALLOW PARALLEL
- Specifies that the database manager must not use parallelism for the function.
- CONCURRENT ACCESS RESOLUTION
- Specifies whether the database manager should wait for data that
is in the process of being updated. DEFAULT is the default.
- DEFAULT
- Specifies that the concurrent access resolution is not explicitly set for this function. The value that is in effect when the function is invoked will be used.
- WAIT FOR OUTCOME
- Specifies that the database manager is to wait for the commit or rollback of data in the process of being updated.
- USE CURRENTLY COMMITTED
- Specifies that the database manager is to use the currently committed version of the data when encountering data that is in the process of being updated.
- When the lock contention is between a read transaction and a delete or update transaction, the clause is applicable to scans with isolation level CS (but not for CS KEEP LOCKS).
- WRAPPED obfuscated-statement-text
- Specifies the encoded definition of the function. A CREATE FUNCTION
statement can be encoded using the WRAP scalar function.
- CARDINALITY integer
- This optional clause provides an estimate
of the expected number of rows to be returned by the function for
optimization purposes. Valid values for integer range from 0 to 2
147 483 647 inclusive.
If the CARDINALITY clause is not specified for a table function, the database manager will assume a finite value as a default.
A table function that returns a row
every time it is called and never returns the end-of-table condition
has infinite cardinality. A query that invokes such a function and
requires an eventual end-of-table condition before it can return any
data will not return unless interrupted. - SET OPTION-statement
- Specifies the options that will be used to create the function.
For example, to create a debuggable function, the following statement
could be included:
SET OPTION DBGVIEW = *SOURCEThe default values for the options depend the options
in effect at create time. For more information, see SET OPTION. The options CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the CREATE FUNCTION statement. CLOSQLCSR(*ENDACTGRP) is always used for SQL table functions.
- SQL-routine-body
- Specifies a single SQL statement, including a compound statement.
See SQL control statements for more information
about defining SQL functions. A non-pipelined table
function must contain exactly one RETURN statement. A pipelined table
function must contain at least one RETURN statement. The RETURN statement
must be executed when the function is invoked.
A call to a procedure that issues a CONNECT, SET CONNECTION, RELEASE, DISCONNECT, COMMIT, ROLLBACK and SET TRANSACTION statement is not allowed in a function.
If the SQL-routine-body is a compound statement, it must contain exactly one RETURN statement and it must be executed when the function is called.
ALTER PROCEDURE (SQL), ALTER FUNCTION (SQL Scalar), and ALTER FUNCTION (SQL Table) with a REPLACE keyword are not allowed in an SQL-routine-body.
Notes
General considerations for defining user-defined functions: See SQL control statements 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.
Pipelined and non-pipelined functions: There are two types of SQL table functions. A table function which does not contain any PIPE statements within the SQL-routine-body is a non-pipelined table function. It contains one RETURN statement which returns a table. A pipelined table function is a table function which contains one or more RETURN statements with no return values and zero or more PIPE statements within the SQL-routine-body. It returns a table a row at a time. The two types of table functions are invoked in exactly the same way.
A PIPE statement returns a result row from the table function. To get the next row, control returns in the SQL-routine-body to the statement following the PIPE statement.
- Any existing comment or label is discarded.
- Authorized users are maintained. The object owner could change.
- Current journal auditing is preserved.
If the function is replaced and the function
signature or result data types are altered, the results from any function,
materialized query table, procedure, trigger, or view that references
the function may be unpredictable. Any referenced objects should be
recreated.
Creating the function: When an SQL function is created, the database manager creates a temporary source file that will contain C source code with embedded SQL statements. A *SRVPGM object is then created using the CRTSRVPGM command. The SQL options used to create the service program are the options that are in effect at the time the CREATE FUNCTION statement is executed. The service program is created with ACTGRP(*CALLER).
When an SQL function is created, the function's
attributes are stored in the created service program object. If the
*SRVPGM object is saved and then restored to this or another system,
the attributes are used to update the catalogs.
The specific name is used to determine the name of the source file member and *SRVPGM object. If the specific name is a valid system name, it will used as the name of member and program. If the member already exists, it will be overlaid. If a program already exists in the specified library, a unique name is generated using the rules for generating system table names. If the specific name is not a valid system name, a unique name is generated using the rules for generating system table names.
Invoking the function: When an SQL function is invoked, it runs in the activation group of the calling program.
Obfuscated statements: A CREATE FUNCTION statement can be executed in obfuscated form. In an obfuscated statement, only the function name and parameters are readable followed by the WRAPPED keyword. The rest of the statement is encoded in such a way that it is not readable but can be decoded by a database server that supports obfuscated statements. Obfuscated statements can be produced by invoking the WRAP scalar function. Any debug options that are specified when the function is created from an obfuscated statement are ignored. A function that is created from an obfuscated statement cannot be restored to a release where obfuscation is not supported.
Dependent objects: An SQL routine is dependent on objects that are referenced in the SQL-routine-body. The names of the dependent objects are stored in catalog view SYSROUTINEDEP. If the object reference in the SQL-routine-body is a fully qualified name or, in SQL naming, if an unqualified name is qualified by the current schema, then the schema name of the object in SYSROUTINEDEP will be set to the specified name or the value of the current schema. Otherwise, the schema name is not set to a specific schema name. If a name is not set to a specific schema name, then DROP and ALTER statements will not be able to determine whether the routine is dependent on the object being altered or dropped.
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
- The keywords VARIANT and NOT VARIANT can be used as synonyms for NOT DETERMINISTIC and DETERMINISTIC.
- The keywords NULL CALL and NOT NULL CALL can be used as synonyms for CALLED ON NULL INPUT and RETURNS NULL ON NULL INPUT.
- The keywords IS DETERMINISTIC may be used as a synonym for DETERMINISTIC.
Example
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
DISALLOW PARALLEL
RETURN
SELECT EMPNO,LASTNAME,FIRSTNME
FROM EMPLOYEE
WHERE EMPLOYEE.WORKDEPT =DEPTEMPLOYEES.DEPTNO