CREATE FUNCTION statement

The CREATE FUNCTION statement creates a Java function, which you can then use in an expression.

The function owner and the database owner automatically gain the EXECUTE privilege on the function, and are able to grant this privilege to other users. The EXECUTE privileges cannot be revoked from the function and database owners.

For details on how Derby matches procedures to Java methods, see Argument matching. For information on how functions interact with deferrable constraints, see Deferrable constraints.

Syntax

CREATE FUNCTION functionName ( [ functionParameter 
   [ , functionParameter ]* [...] ] ) RETURNS returnDataType
[ functionElement ]*

An ellipsis (...) after the last parameter indicates that the Java method supports trailing optional arguments, called varargs. The ellipsis indicates that the method may be invoked with zero or more trailing values, all having the data type of the last argument.

functionParameter

[ parameterName ] dataType

A parameterName must be unique within a function.

The syntax of dataType is described in Data types.

Note: The data types BLOB, CLOB, LONG VARCHAR, LONG VARCHAR FOR BIT DATA, and XML are not allowed as parameters in a CREATE FUNCTION statement.

returnDataType

tableType | dataType

The syntax of dataType is described in Data types.

tableType

TABLE( columnElement [, columnElement ]* )

This is the return type of a table function. Currently, only Derby-style table functions are supported. They are functions which return JDBC ResultSets. For more information, see "Programming Derby-style table functions" in the Derby Developer's Guide.

At runtime, as values are read out of the user-supplied ResultSet, Derby coerces those values to the data types declared in the CREATE FUNCTION statement. This affects values typed as CHAR, VARCHAR, LONG VARCHAR, CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA, and DECIMAL/NUMERIC. Values which are too long are truncated to the maximum length declared in the CREATE FUNCTION statement. In addition, if a String value is returned in the ResultSet for a column of CHAR type and the String is shorter than the declared length of the CHAR column, Derby pads the end of the String with blanks in order to stretch it out to the declared length.

columnElement

SQLIdentifier dataType

The syntax of dataType is described in Data types.

Note: XML is not allowed as the type of a column in the dataset returned by a table function.

functionElement

{
    LANGUAGE JAVA |
    { DETERMINISTIC | NOT DETERMINISTIC } |
    EXTERNAL NAME singleQuotedString |
    PARAMETER STYLE { JAVA | DERBY_JDBC_RESULT_SET | DERBY } |
    EXTERNAL SECURITY { DEFINER | INVOKER } |
    { 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:

LANGUAGE
EXTERNAL NAME
PARAMETER STYLE

LANGUAGE JAVA

The database manager will call the function as a public static method in a Java class.

DETERMINISTIC, NOT DETERMINISTIC

DETERMINISTIC declares that the function is deterministic, meaning that with the same set of input values, it always computes the same result. The default is NOT DETERMINISTIC. Derby cannot recognize whether an operation is actually deterministic, so you must take care to specify this element correctly.

EXTERNAL NAME singleQuotedString

The singleQuotedString specified by the EXTERNAL NAME clause describes the Java method to be called when the function is executed.

PARAMETER STYLE

JAVA: 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.
Derby 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.
DERBY_JDBC_RESULT_SET: The PARAMETER STYLE is DERBY_JDBC_RESULT_SET if and only if this is a Derby-style table function, that is, a function which returns tableType and which is mapped to a method which returns a JDBC ResultSet.
DERBY: The PARAMETER STYLE must be DERBY if and only if an ellipsis (...) appears at the end of the argument list.

EXTERNAL SECURITY

If SQL authorization mode is enabled, a function runs by default with the privileges specified for the user who invokes the function (invoker's rights). To specify that the function should run with the privileges specified for the user who defines the function (definer's rights), create the function with EXTERNAL SECURITY DEFINER. Those privileges include the right to set the current role to a role for which the definer has privileges. When the function is first invoked, no role is set; even if the invoker has set a current role, the function running with definer's rights has no current role set initially.

See derby.database.sqlAuthorization for details about setting SQL authorization mode.

When a function with definer's rights is invoked, the current default schema is set to the eponymously named schema of the definer. For example, if the defining user is called OWNER, the default schema will also be set to OWNER.

When a function with invoker's rights is called, the current default schema and current role are unchanged initially within the function. Similarly, if SQL authorization mode is not enabled, the current default schema is unchanged initially within the function.

When the call returns, any changes made inside the function to the default current schema (and current role, if relevant) are reset (popped).

If SQL authorization mode is not enabled, an attempt to create a function with EXTERNAL SECURITY will result in an error.

NO SQL, CONTAINS SQL, READS SQL DATA

Indicates whether the function issues any SQL statements and, if so, what type.

CONTAINS SQL: Indicates that SQL statements that neither read nor modify SQL data can be executed by the function. Statements that are not supported in any function return a different error.
NO SQL: Indicates that the function cannot execute any SQL statements
READS SQL DATA: Indicates that some SQL statements that do not modify SQL data can be included in the function. Statements that are not supported in any stored function return a different error. This is the default value.

RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT

Specifies whether the function is called if any of the input arguments is null. The result is the null value.

RETURNS NULL ON NULL INPUT: Specifies that the function is not invoked if any of the input arguments is null. The result is the null value.
CALLED ON NULL INPUT: Specifies that the function is invoked if any or all input arguments are null. This specification means that the function must be coded to test for null argument values. The function can return a null or non-null value. This is the default setting.

Example of declaring a scalar function


CREATE FUNCTION TO_DEGREES
( RADIANS DOUBLE )
RETURNS DOUBLE
PARAMETER STYLE JAVA
NO SQL LANGUAGE JAVA
EXTERNAL NAME 'java.lang.Math.toDegrees'

Example of declaring a table function


CREATE FUNCTION PROPERTY_FILE_READER
( FILENAME VARCHAR( 32672 ) )
RETURNS TABLE
  (
     KEY_COL     VARCHAR( 10 ),
     VALUE_COL VARCHAR( 1000 )
  )
LANGUAGE JAVA
PARAMETER STYLE DERBY_JDBC_RESULT_SET
NO SQL
EXTERNAL NAME 'vtis.example.PropertyFileVTI.propertyFileVTI'

Example of declaring a function that takes varargs


CREATE FUNCTION maximum
( a INT ... )
RETURNS INT
LANGUAGE JAVA
PARAMETER STYLE DERBY
NO SQL
EXTERNAL NAME 'IntFunctions.maximum'