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;

With a normal table function, you must select its entire contents. You can, however, write a restricted table function that lets you limit the rows and columns you select. A restricted table function can improve performance greatly. See Writing restricted table functions for details.