Creating a UDF

A UDF is created using the SQL statement CREATE FUNCTION, see "CREATE FUNCTION - Create User Defined Function (UDF)". A UDF can also be created using the SQL statement CREATE SCHEMA, see "CREATE SCHEMA - Create schema".

UDFs can be defined with input parameters.

Recommendation Parameter names should differ from column names (e.g. by assigning a prefix such as par_).

When a UDF is created, the current authorization identifier must have the EXECUTE privilege for the routines called directly in the UDF. It must also, for all tables and columns which are addressed in the UDF, have the (SELECT) privileges which are required to execute the DML statements contained in the routine.

The text of the UDF in SESAM/SQL is written entirely in the SQL programming language. The following SQL statements for data searching are permitted in UDFs, see section "CREATE FUNCTION - Create User Defined Function (UDF)":

SQL statement without a cursor	Function in the UDF	see
SELECT	Reads a single row	"SELECT - Read individual rows"
SQL statement with a cursor
OPEN	Opens a local cursor	"OPEN - Open cursor"
FETCH	Positions a local cursor and, if necessary, reads the current row	"FETCH - Position cursor and read row"
CLOSE	Closes a local cursor	"CLOSE - Close cursor"

Table 26: SQL statements for data manipulation in UDFs

SQL statements for modifying data (INSERT, UPDATE, DELETE, MERGE) are not permitted in the UDFs of SESAM/SQL.

In addition to the SQL statements mentioned above, a procedure can also contain control statements (see section "Control statements in routines") and diagnostic statements (see section "Diagnostic information in routines").

A UDF may not contain any dynamic SQL statements or cursor descriptions, see section "Dynamic SQL".

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.

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.

comments

Descriptive comments (see "Comments") can be inserted in a UDF as required.