User-defined functions

User-defined functions (UDFs) are extensions or additions to the existing built-in functions of the SQL language.

>>-function-name--(--+------------------------+--)-------------><
                     | .-,------------------. |      
                     | V                    | |      
                     '---+-expression-----+-+-'      
                         '-row-expression-'          

A user-defined function can be a scalar function, which returns a single value each time it is called; an aggregate function, which is passed a set of like values and returns a single value for the set; a row function, which returns one row; or a table function, which returns a table.

A number of user-defined functions are provided in the SYSFUN and SYSPROC schemas.

A UDF can be an aggregate function only if it is sourced on an existing aggregate function. A UDF is referenced by means of a qualified or unqualified function name, followed by parentheses enclosing the function arguments (if any). A user-defined column or scalar function registered with the database can be referenced in the same contexts in which any built-in function can appear. A user-defined row function can be referenced only implicitly when registered as a transform function for a user-defined type. A user-defined table function registered with the database can be referenced only in the FROM clause of a SELECT statement.

Function arguments must correspond in number and position to the parameters specified for the user-defined function when it was registered with the database. In addition, the arguments must be of data types that are promotable to the data types of the corresponding defined parameters.

The result of the function is specified in the RETURNS clause. The RETURNS clause, defined when the UDF was registered, determines whether or not a function is a table function. If the RETURNS NULL ON NULL INPUT clause is specified (or defaulted to) when the function is registered, the result is null if any argument is null. In the case of table functions, this is interpreted to mean a return table with no rows (that is, an empty table).

See "Row expressions" for more information about rules and row data types.

Following are some examples of user-defined functions:

• A scalar UDF called ADDRESS extracts the home address from resumes stored in script format. The ADDRESS function expects a CLOB argument and returns a VARCHAR(4000) value:

     SELECT EMPNO, ADDRESS(RESUME) FROM EMP_RESUME
       WHERE RESUME_FORMAT = 'SCRIPT'

• Table T2 has a numeric column A. Invoking the scalar UDF called ADDRESS from the previous example:

     SELECT ADDRESS(A) FROM T2

  raises an error (SQLSTATE 42884), because no function with a matching name and with a parameter that is promotable from the argument exists.
• A table UDF called WHO returns information about the sessions on the server machine that were active at the time that the statement is executed. The WHO function is invoked from within a FROM clause that includes the keyword TABLE and a mandatory correlation variable. The column names of the WHO() table were defined in the CREATE FUNCTION statement.

     SELECT ID, START_DATE, ORIG_MACHINE
       FROM TABLE( WHO() ) AS QQ
       WHERE START_DATE LIKE 'MAY%'