Invoking user-defined table functions

Once the user-defined table function is written and registered with the database, you can invoke it in the FROM clause of a SELECT statement.

Before you begin

The table function must have been created in the database by executing the CREATE FUNCTION.
For external user-defined table functions, the library or class file associated with the function must be in the location specified by the EXTERNAL clause of the CREATE FUNCTION.
To invoke a user-defined table function a user must have EXECUTE privilege on the function. For more privilege related information see the CREATE FUNCTION reference.

Restrictions

For restrictions on invoking user-defined table functions, see the CREATE FUNCTION topics in the related links.

Procedure

To invoke a user-defined table function, reference the function in the FROM clause of an SQL statement where it is to process a set of input values. The reference to the table function must be preceded by the TABLE clause and be contained in brackets.

For example, the following CREATE FUNCTION statement defines a table function that returns the employees in a specified department number.

  CREATE FUNCTION DEPTEMPLOYEES (DEPTNO VARCHAR(3))
    RETURNS TABLE (EMPNO CHAR(6),
                   LASTNAME VARCHAR(15),
                   FIRSTNAME VARCHAR(12))
    LANGUAGE SQL
    READS SQL DATA
    NO EXTERNAL ACTION
    DETERMINISTIC
    RETURN
      SELECT EMPNO, LASTNAME, FIRSTNME FROM EMPLOYEE
        WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO

The following is a SELECT statement that makes use of DEPTEMPLOYEES:

  SELECT EMPNO, LASTNAME, FIRSTNAME FROM TABLE(DEPTEMPLOYEES('A00')) AS D