SQL Reference

User-Defined Functions

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

User-defined functions are extensions or additions to the existing built-in functions of the SQL language. A user-defined function can be a scalar function, which returns a single value each time it is called, a column 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. Note that a UDF can be a column function only when it is sourced on an existing column function.

A user-defined scalar or column function registered with the database can be referenced in the same contexts that any built-in function can appear.

A user-defined table function registered with the database can be referenced only in the FROM clause of a SELECT, as described in from-clause.

A user-defined row function can be referenced only implicitly when registered as a transform function for a user-defined type.

A user-defined function is referenced by means of a qualified or unqualified function name, followed by parentheses enclosing the function arguments (if any).

Arguments of the function must correspond in number and position to the parameters specified in the user-defined function as it was registered with the database. In addition, the arguments must be of data types promotable to the data types of the corresponding defined parameters. (see CREATE FUNCTION).

The result of the function is as specified in the RETURNS clause specified when the user-defined function was registered. The RETURNS clause determines if a function is a table function or not.

If the RETURNS NULL ON NULL INPUT clause was specified (or defaulted to) when the function was registered then, if any argument is null, the result is null. For table functions, this is interpreted to mean a return table with no rows (empty table).

There are a collection of user-defined functions provided in the SYSFUN schema (see Table 15).

Examples:

• Assume that a scalar function called ADDRESS was written to extract the home address from a script format resume. The ADDRESS function expects a CLOB argument and returns a VARCHAR(4000). The following example illustrates the invocation of the ADDRESS function.

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

• Assume a table T2 with a numeric column A and the ADDRESS function described in the previous example. The following example illustrates an attempt to invoke the ADDRESS function with an incorrect argument.

     SELECT ADDRESS(A) FROM T2
  

  An error (SQLSTATE 42884) is raised since there is no function with a matching name and with a parameter promotable from the argument.

• Assume a table function WHO was written to return information about the sessions on the server machine which were active at the time the statement is executed. The following example illustrates the invocation of WHO in a FROM clause (TABLE keyword with mandatory correlation variable).

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

  The column names of the WHO() table are defined in the CREATE FUNCTION statement.