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.
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;