Overview of Derby-style table functions

A Derby-style table function is a method which returns a JDBC ResultSet.

Most of the ResultSet methods can be written as stubs which simply raise exceptions. However, the Derby-style table function must implement the following ResultSet methods:

• next()
• close()
• wasNull()
• getXXX() - When invoking a Derby-style table function at runtime, Derby calls a getXXX() method on each referenced column. The particular getXXX() method is based on the column's data type as declared in the CREATE FUNCTION statement. Preferred getXXX() methods for Derby-style table functions explains how Derby selects an appropriate getXXX() method. However, nothing prevents application code from calling other getXXX() methods on the ResultSet. The returned ResultSet needs to implement the getXXX() methods which Derby will call as well as all getXXX() methods which the application will call.

A Derby-style table function is materialized by a public static method which returns a ResultSet:

public static ResultSet read() {...}

The public static method is then bound to a Derby function name:

CREATE FUNCTION externalEmployees
()
RETURNS TABLE
(
  employeeId    INT,
  lastName      VARCHAR( 50 ),
  firstName     VARCHAR( 50 ),
  birthday      DATE
)
LANGUAGE JAVA
PARAMETER STYLE DERBY_JDBC_RESULT_SET
READS SQL DATA
EXTERNAL NAME 'com.acme.hrSchema.EmployeeTable.read'

To invoke a table function, wrap it in a TABLE constructor in the FROM list of a query. Note that the table alias (in this example "s") is a required part of the syntax:

INSERT INTO employees
  SELECT s.*
    FROM TABLE (externalEmployees() ) s;

• Preferred getXXX() methods for Derby-style table functions
  While scanning a Derby-style table function, Derby calls a preferred getXXX() method for each column, based on the column's data type. If Derby is running on a small device platform and presenting the JSR 169 interface to clients, then the methods which Derby calls are slightly different. This is because JSR 169 does not support BigDecimal.