CREATE FUNCTION

The CREATE FUNCTION statement allows you to create Java functions, which you can then use in expressions.

For details on how Splice Machine matches functions to Java methods, see Argument matching.

Syntax

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

functionName

If schemaName is not provided, then the current schema is the default schema. If a qualified procedure name is specified, the schema name cannot begin with SYS.

functionParameter

[ parameterName ] DataType

parameterName is an identifier that must be unique within the function’s parameter names.

Data-types such as BLOB, CLOB, LONG VARCHAR are not allowed as parameters in a CREATE FUNCTION statement.

returnDataType

functionElement

See the description of Function Elements in the next section.

TableType

TABLE( ColumnElement [,ColumnElement]* )

ColumnElement

A SQL Identifier.

Table functions return TableType results. Currently, only Splice Machine-style table functions are supported, which are functions that return JDBC ResultSets.

When values are extracted from a ResultSet, the data types of the values are coerced to match the data types declared in the CREATE FUNCTION statement. Here are a few coercion rules you should know about:

  • values that are too long are truncated to the maximum declared length
  • if a string value is returned in the ResultSet for a column of type CHAR, and the string is shorter than the column length, the string is padded with spaces

Function Elements

 {
    LANGUAGE { JAVA }
  | EXTERNAL NAME javaMethodName
  | DeterministicCharacteristic
  | PARAMETER STYLE parameterStyle
  | sqlStatementType
  | nullInputAction
}

The function elements may appear in any order, but each type of element can only appear once.

These function elements are required:

  • LANGUAGE
  • EXTERNAL NAME
  • PARAMETER STYLE

LANGUAGE

Specify the language in which your function is written; this must be JAVA.

DeterministicCharacteristic

DETERMINISTIC | NOT DETERMINISTIC

The default value is NOT DETERMINISTIC.

Specifying DETERMINISTIC indicates that the function always returns the same result, given the same input values. This allows Splice Machine to call the function with greater efficiency; however, specifying this for a function that is actually non-deterministic will have the opposite effect – efficiency of calls to the function will be reduced.

javaMethodName

class_name.method_name

This is the name of the Java method to call when this function executes.

parameterStyle

JAVA | DERBY_JDBC_RESULT_SET

Only use DERBY_JDBC_RESULT_SET if this is a Splice Machine-style table function that returns a TableType result, and is mapped to a Java method that returns a JDBC ResultSet.

Otherwise, use JAVA-style parameters, which means that a parameter-passing convention is used that conforms to the Java language and SQL Routines specification. INOUT and OUT parameters are 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.

Splice Machine does not support long column types such as LONG VARCHARor BLOB; an error will occur if you try to use one of these long column types.

sqlStatementType

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.

nullInputAction

RETURNS NULL ON NULL INPUT

If any input argument is null, the function is not invoked, and the result is null.

CALLED ON NULL INPUT

This is the default value.

The function is invoked even if all input arguments are null, which means that the invoked function must test for null argument values.The result may be null or not null.

Example of declaring a scalar function

For more complete examples of using CREATE FUNCTION, please see the Using Functions and Stored Procedures section of our Developer’s Guide.

JAVA Example

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

0 rows inserted/updated/deleted

Example of declaring a table function

This example reads data from a mySql database and inserts it into a Splice Machine database.

We first implement a class that contains a public static method that connects to an external (foreign) database, uses a prepared statement to pull results from it, and returns those results as a JDBC ResultSet:

package splicemachine.example.vti;
import java.sql.*;
public class EmployeeTable{
    public static ResultSet read()  throws SQLException {
        Connection conn DriverManager.getConnection(
                "jdbc:mysql://localhost/hr?user=myName&password=myPswd" );
        PreparedStatement ps = conn.prepareStatement(
                "SELECT * FROM hrSchema.EmployeeTable" );
        return ps.executeQuery();
    }
}

Next we use the  CREATE FUNCTION .statement to declare a table function to read data from our external database and insert it into our Splice Machine database:

CREATE FUNCTION externalEmployees()
   RETURNS TABLE
     (
      employeeId    INT,
      lastName      VARCHAR( 50 ),
      firstName     VARCHAR( 50 ),
      birthday      DATE
     )
   LANGUAGE JAVA
   PARAMETER STYLE SPLICE_JDBC_RESULT_SET   READS SQL DATA   EXTERNAL NAME 'com.splicemachine.example.vti.readEmployees';

Now we’re ready to invoke our table function to read data from the external database and insert it into a table in our Splice Machine database.

To invoke a table function, you must wrap it in a TABLE constructor in the FROM list of a query. For example, we could insert employee data from that database into a table named employees in our Splice Machine database:

INSERT INTO employees
  SELECT myExtTbl.*
    FROM TABLE (externalEmployees() ) myExtTbl;

You MUST specify the table alias when using a virtual table; for example, myExtTbl in the above example.

See Also