CREATE FUNCTION

Creates java functions that can be invoked from other SQL expressions.

Syntax

CREATE FUNCTION function-name 
    ( [ function-parameter [ , function-parameter ] ] * )
RETURNS data-type
[ function-element ] *

Description

This statement creates java functions that can be invoked from other SQL expressions.

function-name

[ schema-name . ] identifier

A qualified function name cannot use the SYS schema, which is reserved for internal functions.

function-parameter

[ parameter-name ] DataType

function-element

{
LANGUAGE JAVA
|  EXTERNAL NAME 'external_name'
|  PARAMETER STYLE parameter-style
|  { NO SQL | CONTAINS SQL | READS SQL DATA }
|  { RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT }
}

The function elements may appear in any order, but each type of element can only appear once. A function definition must contain these elements:


parameter-style

JAVA | DERBY_JDBC_RESULT_SET
The function will use a parameter-passing convention that conforms to the Java language and SQL Routines specification. INOUT and OUT parameters will be passed as single entry arrays to facilitate returning values. Result sets can be returned through additional parameters to the Java method of type java.sql.ResultSet[] that are passed single entry arrays.

SQLFire does not support long column types (for example Long Varchar, BLOB, and so on). An error will occur if you try to use one of these long column types.

The PARAMETER STYLE is DERBY_JDBC_RESULT_SET if and only if this is a SQLFire table function, that is, a function which returns TableType and which is mapped to a method which returns a JDBC ResultSet. Otherwise, the PARAMETER STYLE must be JAVA. See Using Table Functions to Import Data as a SQLFire Tables.