CREATE FUNCTION

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > CREATE statements >

CREATE FUNCTION

Previous pageReturn to chapter overviewNext page

SQLstream provides a number of built-in functions, and also allows users to extend s-Server's capabilities by means of user-defined functions (UDFs) and user-defined transformations (UDXs). You define both using CREATE FUNCTION. Functions must be declared within schemas, but can be added to a SQL path.

Values passed to (or returned from) a user-defined function or transformation must be exactly the same data types as the corresponding parameter definitions. In other words, implicit casting is not allowed in passing parameters to (or returning values from) a user-defined function or a user-defined transform.

User-defined functions and transformations may be invoked using either the fully-qualified name (<schema>.<function_name>) or by the function name alone if it exists in the current SQL path (see SET PATH and CURRENT_PATH).

s-Server follows standard SQL rules for how SQL scalar parameters in the CREATE FUNCTION statement are mapped to Java method arguments. These are summarized in a table below. See https://db.apache.org/derby/docs/10.12/ref/rrefsqljargmatching.html for more details.

User-Defined Function (UDF)

A user-defined function can implement complex calculations or interact with an external system, taking zero or more scalar parameters and returning a scalar result. UDFs operate like built-in functions such as FLOOR() or LOWER(). For each occurrence of a user-defined function within a SQL statement, that UDF is called once per row with scalar parameters (constants or column values in that row).

User-defined functions may be defined either directly in SQL, or externally with Java classes.

Note: any Java class referenced by a UDF must be explicitly defined as part of a package (using a package clause in the source file for the Java class).

SQL Functions

Syntax

CREATE FUNCTION ''<function_name>'' ( ''<parameter_list>'' )

 RETURNS ''<data type>''

 LANGUAGE SQL

 [ SPECIFIC ''<specific_function_name>''  | [NOT] DETERMINISTIC ]

 CONTAINS SQL

 [ READS SQL DATA ]

 [ MODIFIES SQL DATA ]

 [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]

 RETURN ''<SQL-defined function body>''

 

SPECIFIC assigns a specific function name that is unique within the database. Note that the regular function name does not need to be unique (two or more functions may share the same name, as long as they are distinguishable by their parameter list). The specific name may not be used to invoke the function, but is useful when dropping a function (see DROP FUNCTION) when the regular function name is not unique.

DETERMINISTIC / NOT DETERMINISTIC indicates whether a function will always return the same result for a given set of parameter values. This may be used internally in query optimization.

READS SQL DATA and MODIFIES SQL DATA indicate whether the function potentially reads or modifies SQL data, respectively. If a function attempts to read data from tables, streams, or views without READS SQL DATA being specified, or insert to a stream or modify a table without MODIFIES SQL DATA being specified, an exception will be raised.

RETURNS NULL ON NULL INPUT and CALLED ON NULL INPUT indicate whether the function is defined as returning null if any of its parameters are null. If left unspecified, the default is CALLED ON NULL INPUT.

A SQL-defined function body consists only of a single RETURN statement.

Examples

CREATE FUNCTION get_fraction( degrees DOUBLE )

   RETURNS DOUBLE

   CONTAINS SQL

   RETURN degrees - FLOOR(degrees)

;

 

Java Functions

Syntax

CREATE FUNCTION <function_name> ( <parameter_list> )

 RETURNS <built-in scalar type>

 LANGUAGE  JAVA

 PARAMETER STYLE SYSTEM DEFINED JAVA

 [ SPECIFIC <specific_function_name>  | [NOT] DETERMINISTIC ]

 NO SQL

 [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]

 EXTERNAL NAME ['class <fully qualified static method name> '

 | '<qualified jar name>:<fully qualified static method name>']

 

Any parameters to a Java-defined external function must be specified using built-in data types. While the Java function can return a value, changes to any values passed to the function as parameters only exist internal to the Java function. They are not passed back to the calling SQL.

SPECIFIC assigns a specific function name that is unique within the database. Note that the regular function name does not need to be unique (two or more functions may share the same name, as long as they are distinguishable by their parameter list). The specific name may not be used to invoke the function, but is useful when dropping a function (see DROP FUNCTION) when the regular function name is not unique.

DETERMINISTIC / NOT DETERMINISTIC indicates whether a function will always return the same result for a given set of parameter values. This may be used internally in query optimization.

RETURNS NULL ON NULL INPUT and CALLED ON NULL INPUT indicate whether the function is defined as returning null if any of its parameters are null. If left unspecified, the default is CALLED ON NULL INPUT.

EXTERNAL NAME is used to define where SQLstream s-Server should look for the Java class implementing the function. Java-defined external functions must be located either in a class file on SQLstream s-Server's Java classpath, or in an external Java archive (JAR) file loaded into the system using CREATE JAR. In the latter case, the qualified jar name is the name given to the jar as part of CREATE FUNCTION. If the jar name was not defined in the current schema, then the fully qualified <schema>.<jar name> format must be used.

The Java class containing the method must live inside a package.

The following EXTERNAL NAME clauses will work:

EXTERNAL NAME '"FOO_SCHEMA":foo.VarbinaryFunctions.toUTF8String'

 

EXTERNAL NAME '"FOO_SCHEMA":foo.bar.VarbinaryFunctions.toUTF8String'

 

but the following declaration will raise an error when you try to invoke the function:

EXTERNAL NAME '"FOO_SCHEMA":VarbinaryFunctions.toUTF8String'

 

Examples

CREATE FUNCTION get_fraction( degrees DOUBLE )

   RETURNS DOUBLE

   LANGUAGE JAVA

   NO SQL

   EXTERNAL NAME 'GPSFuncsJavaLib:com.sqlstream.examples.geolocate.Degree.getFraction'

;

CREATE FUNCTION get_fraction( degrees DOUBLE )

   RETURNS DOUBLE

   LANGUAGE JAVA

   NO SQL

   EXTERNAL NAME 'class com.sqlstream.examples.geolocate.Degree.getFraction'

;

Note that the above examples only cover the CREATE FUNCTION call. To fully define a Java UDF, you need to create and define a Java class (such as com.sqlstream.examples.geolocate.Degree) using CREATE JAR. See the topic GeoLocation: An Example of User-Defined Functions  in the s-Server Integration Guide for full examples covering the entire process.

User-Defined Transformation (UDX)

A user-defined transform is a user-defined function that returns a set of rows or a stream of rows. Its input arguments can be scalars or cursors. A cursor is an object representing a subquery, which the user-defined transform can use to read the subquery results. The subquery can be relational or streaming.

A user-defined transform is always implemented by a Java class, which must be located in a java package.

A UDX is declared by a CREATE FUNCTION statement, much like a user-defined function.

The salient differences are:

A scalar input is declared as a parameter with a scalar type, just as for a UDF.
A subquery input, whether relational or streaming, is declared with type CURSOR.
The rowtype of a cursor is not stated. Indeed, the rowtype of a cursor is dynamic, and is bound when the user-defined transform is applied to a subquery.
In practical use, a user-defined transform is written in Java to expect specific rowtypes. There is no way to state this constraint in the DDL.
The output of a user-defined transform is a virtual table or stream. The output columns and types are specified in a RETURNS TABLE clause.
When a user-defined transform has an input CURSOR c, the output rowtype can say c.*   to mean, all the columns from that input, in the same order as in the catalog.
A user-defined transform can take any number of relational inputs, and can process their rows in any order (fetching all of one first, and then moving on to the next, or interleaving fetches).
UDX output is not tied to input processing. A user-defined transform can emit rows as needed.

UDXes, Streams, and Rowtimes

A stream always has a rowtime column. Therefore, a cursor based on a streaming query will have a rowtime, but a cursor based on a relational query will not. As a result, the SQL that calls the UDX differs:

CURSOR(SELECT STREAM ....)  has a rowtime

CURSOR(SELECT ....)  has no rowtime.

 

The declaration of a UDX states its output rowtype, although with the "c.*" construct the output can depend dynamically on the input type. The rowtype of an input cursor is always dynamic.

A UDX is required to set the value of every output column, matching its declared data type. ROWTIME is special:

It occurs only in streaming context;
It is never null and never decreases;
The UDX may set it (often by copying the ROWTIME from an input cursor); OR
The UDX may skip it, which means the system will set it to the current time.

Good practice for coding a Java UDX is:

Check the rowtype of inputs (java.sql.ResultSet) and of the output (java.sql.PreparedStatement).
Refer to columns by name, not position.
An output ROWTIME indicates the UDX has been called in a streaming context.

Syntax

CREATE FUNCTION ''function_name'' ( ''parameter_list'' )

  RETURNS TABLE ( ''TableFunctionColumnList'' )

  LANGUAGE  JAVA

  PARAMETER STYLE SYSTEM DEFINED JAVA

  [ SPECIFIC ''specific_function_name''  | [NOT] DETERMINISTIC ]

  NO SQL

  [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]

  EXTERNAL NAME ['class <fully qualified class name> '

               | ' <qualified jar name>:<fully qualified class name> ']

 

SPECIFIC assigns a specific function name that is unique within the database. Note that the regular function name does not need to be unique (two or more functions may share the same name, as long as they are distinguishable by their parameter list). The specific name may not be used to invoke the function, but is useful when dropping a function (see DROP FUNCTION) when the regular function name is not unique.

DETERMINISTIC / NOT DETERMINISTIC indicates whether a function will always return the same result for a given set of parameter values. This may be used internally in query optimization.

RETURNS NULL ON NULL INPUT and CALLED ON NULL INPUT indicate whether the function is defined as returning null if any of its parameters are null. If left unspecified, the default is CALLED ON NULL INPUT.

EXTERNAL NAME is used to define where SQLstream s-Server should look for the public Java class implementing the UDX. Java-defined external functions must be located either in a class file on SQLstream s-Server's Java classpath, or in an external Java archive (JAR) file loaded into the system using CREATE JAR. In the latter case, the qualified jar name is the name given to the jar as part of CREATE FUNCTION. If the jar name was not defined in the current schema, then the fully qualified <schema>.<jar name> format must be used.

Examples

The following examples only cover the CREATE FUNCTION call. To fully define a Java UDX, the Java class specified in the EXTERNAL NAME clause must also be created and loaded into SQLstream s-Server. See the SDK topic of the SQLstream s-Server Integration Guide for full examples covering the entire process.

The examples presented in this topic refer to the following stream definitions:

CREATE STREAM transmitStream (

   bytesSent INTEGER,

   data_sent VARBINARY(1024));

CREATE STREAM receiveStream (

   bytesRecieved INTEGER,

   data_received VARBINARY(1024));

 

The first example defines a UDX named addTimeStamp. The input is the result of a query expression, and the output is the same set of columns as the input with the addition of a new column named ts of type TIMESTAMP. Note the use of the c.* construct. It indicates that all columns from the input expression will be present in the output, in this case with the addition of one column for a new TIMESTAMP value. Note that does not mean that the values are echoed from input to output unchanged. It only indicates that all columns defined for the input relation will be present in the output.

CREATE OR REPLACE FUNCTION addTimeStamp(c CURSOR)

  RETURNS TABLE(c.*, ts TIMESTAMP)

  LANGUAGE JAVA

  PARAMETER STYLE SYSTEM DEFINED JAVA

  NO SQL

  EXTERNAL NAME 'class com.sqlstream.udxsample.UdxSample.addTimestamp';

 

Usage:

SELECT STREAM * FROM TABLE(addTimeStamp(CURSOR(SELECT STREAM * FROM transmitStream)));

 

Output Row:

   bytesSent INTEGER

   data_sent VARBINARY(1024)

   ts TIMESTAMP

 

The next example shows the declaration and use of a UDX that has three inputs: one scalar integer value and two query expressions. Also note that in this example the output is fixed at 3 columns.

CREATE OR REPLACE FUNCTION crossRef(bias INTEGER, c1 CURSOR, c2 CURSOR)

  RETURNS TABLE(leftVal INTEGER, rightVal INTEGER, position DECIMAL(9,2))

  LANGUAGE JAVA

  PARAMETER STYLE SYSTEM DEFINED JAVA

  NO SQL

  EXTERNAL NAME 'class com.sqlstream.udxsample.UdxSample.crossRef';

 

Use:

SELECT STREAM res.position

FROM TABLE(crossRef( 33, CURSOR(SELECT STREAM * FROM transmitStream),

                        CURSOR(SELECT STREAM * FROM recieveStream))) AS res;

Output Row:

   leftVal INTEGER

   rightVal INTEGER

   position DECIMAL(9,2)

 

The following example has two relational inputs. The output row consists of all columns defined by both CURSORs as well as an additional DECIMAL column.

Note: In functions with multiple relational inputs, column names must be distinct for each relational input.

CREATE OR REPLACE FUNCTION confirmResults(c1 CURSOR, c2 CURSOR)

  RETURNS TABLE(c1.*, c2.*, confidence DECIMAL(5,2))

  LANGUAGE JAVA

  PARAMETER STYLE SYSTEM DEFINED JAVA

  NO SQL

  EXTERNAL NAME 'class com.sqlstream.udxsample.UdxSample.confirmResults';

 

Use:

SELECT STREAM res.position

FROM TABLE(confirmResults(CURSOR(SELECT STREAM * FROM transmitStream),

                        CURSOR(SELECT STREAM * FROM receiveStream))) AS res;

 

Output Row:

   bytesSent INTEGER

   data_sent VARBINARY(1024)

   bytesRecieved INTEGER

   data_received VARBINARY(1024)

   confidence DECIMAL(5,2)

Mapping SQL Scalar Parameters to Java Method Arguments

s-Server follows the SQL standard in regards to mapping SQL scalar parameters to Java method arguments. These are described in detail here

https://db.apache.org/derby/docs/10.12/ref/rrefsqljargmatching.html and summarized in the table below.

Note: s-Server does not support the creation of columns with interval data types. This is consistent with the JDBC specification, which also does not have support for the INTERVAL data type.

SQL Type

Primitive Match

Wrapper Match

BOOLEAN

boolean

java.lang.Boolean

SMALLINT

short

java.lang.Integer

INTEGER

int

java.lang.Integer

BIGINT

long

java.lang.Long

DECIMAL

None

java.math.BigDecimal

NUMERIC

None

java.math.BigDecimal

REAL

float

java.lang.Float

DOUBLE

double

java.lang.Double

FLOAT

double

java.lang.Double

CHAR

None

java.lang.String

VARCHAR

None

java.lang.String

CHAR

byte[]

None

DATE

None

java.sql.Date

TIME

None

java.sql.Time

TIMESTAMP

None

java.sql.Timestamp