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:

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.