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
matches procedures to Java methods, see
. For
information on how functions interact with deferrable constraints, see
.
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 .
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 .
tableType
TABLE( columnElement [, columnElement ]* )
This is the return type of a table function. Currently, only
-style table functions
are supported. They are functions which return JDBC ResultSets. For more
information, see "Programming
-style table functions"
in the .
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
.
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 JAVAThe 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.
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 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.
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
-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 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 INPUTSpecifies
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'