Most of the ResultSet methods can be
written as stubs which simply raise exceptions. However, the
-style table function
must implement the following ResultSet methods:
- next()
- close()
- wasNull()
- getXXX() - When invoking a
-style table
function at runtime,
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 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
will call as well
as all getXXX() methods which the application will call.
A -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
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.example.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 for details.