Writing table functions

SQL table function example

SQL table functions are contained in a deployment descriptor file. This is an example of an SQL table function that produces a result set consisting of parts and supply information

CREATE FUNCTION parts_supplied()
RETURNS TABLE(PNO CHAR(2), PNAME CHAR(10), SNO CHAR(2), QTY INTEGER)
LANGUAGE SQL
PARAMETER STYLE SQL
READS SQL DATA
DETERMINISTIC
RETURN
  SELECT P.PNO, P.PNAME, SP.SNO, SP.QTY
  FROM PARTS P, SUPPLY SP
  WHERE P.PNO = SP.PNO;

This table function can be used in the FROM clause of a SQL statement. For example,

SELECT PNO, PNAME, SNO, QTY
FROM TABLE( parts_supplied() ) T
WHERE QTY > 200

Java table functions

Java table functions are implemented as static methods whose return type is an object of a class that implements the java.sql.ResultSet interface. The input parameters can be any primitive types or objects that map to a supported dynamic query extensibility data type. See Data type conversions from JDBC/SQL data types to Java data types for a list of supported data type mappings.

Java table function example

This function enumerates all of the locale information for the currently running Java™ Runtime Environment. The function returns a row for each locale consisting of the country, language, country code, language code, and currency code. The Java code is included in the Locales.java sample program.

The following lines in a deployment description file are associated with this example.

CREATE FUNCTION enumerateLocales()
RETURNS
TABLE(
COUNTRY VARCHAR(128),
"LANGUAGE" VARCHAR(128),
COUNTRY_CODE VARCHAR(32),
LANGUAGE_CODE VARCHAR(32),
CURRENCY_CODE VARCHAR(32)
)
LANGUAGE JAVA
PARAMETER STYLE JAVAEXTERNAL NAME 'thisjar:udf.samples.Locales.enumerateLocales';

SELECT *
FROM TABLE( enumerateLocales() ) T