CREATE FUNCTION creates a UDF and saves its definition in the database.
UDFs and their use in SESAM/SQL are described in detail in chapter "Routines".
Each routine which is called in the UDF must already exist. Nested calls of routines are thus possible, but rerecursive calls are not.
The current authorization identifier must own the schema to which the UDF belongs. It must also, for all tables and columns which are addressed in the UDF, have the privileges which are required to execute the DML statements contained in the UDF.
The current authorization identifier must have the EXECUTE privilege for the routine called directly in the UDF. It must also, for all tables and columns which are addressed in the UDF, have the privileges which are required to execute the DML statements contained in the routine.
The current authorization identifier automatically obtains the EXECUTE privilege for the UDF created. If it even has authorization to pass on the relevant privileges, it may also pass on the EXECUTE privilege to other authorization identifiers.
The UDF and the objects which are addressed in the UDF must belong to the same database. The names of these objects may possibly be complemented by the UDF's database and schema names.
CREATE FUNCTION
udf ([ udf_parameter_definition [, udf_parameter_definition ]...])
RETURNS data_type
{ READS SQL DATA | CONTAINS SQL }
{ routine_sql_statement | compound_statement }
udf ::= routine
udf_parameter_definition ::= [IN] routine_parameter data_type
udf
Name of the UDF (maximum length: 31 characters). The unqualified name of the UDF must be different from the other routine names in the schema. You can qualify the table name with a database and schema name.
If the CREATE FUNCTION statement is specified in a CREATE SCHEMA statement, the UDF name may be qualified only with the database and schema names from the CREATE SCHEMA statement.
([ udf_parameter_definition [{, udf_parameter_definition }...]])
List of the UDF call parameters. Any number of UDF parameters is possible. It is limited only by the maximum statement length. If no parameter is defined, the list consists only of the parentheses.
udf_parameter_definition
Definition of a UDF call parameter.
UDF call parameters have no indicator variable.
routine_parameter
Name of the UDF call parameter. The names of the UDF call parameters must differ from each other.
data_type
Data type of the UDF call parameter.
Only unqualified UDF call parameters are permitted.
dimension may not be specified.
RETURNS data_type
Data type of the UDF return value.
Only unqualified UDF return values are permitted.
dimension may not be specified.
READS SQL DATA
The UDF can contain SQL statements for reading data, but no SQL statements for updating data. This information is checked. In the event of an error, the statement is rejected with SQLSTATE.
CONTAINS SQL
The UDF contains neither SQL statements for reading data nor for updating data. This information is checked. In the event of an error, the statement is rejected with SQLSTATE.
Called routines of this procedure may not contain the MODIFIES SQL DATA and READS SQL DATA specifications.
routine_sql_statement
A UDF contains precisely one non-atomic SQL statement or precisely one RETURN statement. The non-atomic SQL statement must contain at least one RETURN statement. The non-atomic SQL statements in SESAM/SQL are COMPOUND (without specification of ATOMIC), CASE, FOR, IF, LOOP, REPEAT, and WHILE. They can contain other (atomic or non-atomic) SQL statements. Atomic SQL statements are the other SQL statements permissible in a routine.
No privileges are checked before an SQL statement is executed.
An SQL statement in a UDF may access the parameters of the UDF and (if the statement is part of a COMPOUND statement) local variables, but not host variables.
The syntax and meaning of routine_sql_statement are described centrally in section "SQL statements in routines". The SQL statements named there, with the exception of the SQL statements for modifying data (INSERT, UPDATE, MERGE, DELETE), may be used.
compound_statement
COMPOUND statement which contains multiple SQL statements and possibly defines common local data, cursors, and exception handling routines for these, see section "COMPOUND - Execute SQL statements in a common context".
Conditions
SESAM/SQL offers the SQL statements COMPOUND, CASE, FOR, IF, ITERATE, LEAVE, LOOP, REPEAT, SET, and WHILE for controlling routines. These SQL statements are also referred to as control statements.
You obtain diagnostic information in routines with the diagnostic statements GET DIAGNOSTICS, SIGNAL, and RESIGNAL.
In SESAM/SQL, nested calls of routines are permitted. The CALL statement is therefore one of the statements permitted in a routine.
A routine may not contain any SQL statements for transaction management (see "SQL statements for transaction management"). Local cursors can therefore not be accessed on a cross-transaction basis. STORE or RESTORE statements are not statements which are permitted in a routine; their use in a routine makes no sense.
A routine may not contain any dynamic SQL statements or cursor descriptions, see section "Dynamic SQL".
A routine can be called in a UDF in a dynamic SQL statement. If a procedure contains parameters of the type OUT or INOUT, the corresponding arguments must be specified in a dynamic CALL statement in the form of placeholders.
Example
The GetCurrentYear UDF below returns the current year as a number. It contains no SQL statements for reading or updating data.
CREATE FUNCTION GetCurrentYear (IN "TIME" TIMESTAMP(3)) RETURNS DECIMAL(4) CONTAINS SQL RETURN EXTRACT (YEAR FROM "TIME")
You will find further examples in chapter "Routines" and in the demonstration database of SESAM/SQL (see the “ Core manual”).
See also
DROP FUNCTION, COMPOUND, CASE, FOR, IF, ITERATE, LEAVE, LOOP, REPEAT, SET, WHILE, CALL, RETURN, SELECT, INSERT, OPEN, FETCH, CLOSE, GET DIAGNOSTICS, SIGNAL, RESIGNAL