CREATE FUNCTION

Documentation

VoltDB Home » Documentation » Using VoltDB

CREATE FUNCTION

CREATE FUNCTION — Defines a SQL function and associates it with a Java method.

Synopsis

CREATE FUNCTION function-name FROM METHOD class-path.method-name

Description

The CREATE FUNCTION statement declares a user-defined function and associates it with a Java method. The return value of the function matches the datatype of the Java method itself. Similarly, the number and datatype of the function's arguments are defined by the arguments of the method.

User-defined functions allow you to extend the functionality of the SQL language by declaring your own functions that can be used in SQL queries and data manipulation statements. The steps for creating a user-defined function are:

  1. Write, compile, and debug the program code for the method that will perform the function's action.

  2. Package the class and method in a JAR file, just as you would a stored procedure. (Classes for functions and stored procedures can be packaged in the same JAR file.)

  3. Load the JAR file into the database using the LOAD CLASSES statement.

  4. Declare and name the user-defined function using the CREATE FUNCTION statement.

For example, let's say you want to create function that decodes an HTML-encoded string. The beginning of the Java method might look like this, declaring a method of type String and accepting two arguments: the string to encode and an integer value for the maximum length.

package myapp.datatypes;

  public class Html {
 
    public String decode( String html, int maxlength )
                  throws VoltAbortException {

After compiling and packaging this class into a JAR file, you can load the class and declare it as a SQL function:

sqlcmd
1> LOAD CLASSES myfunctions.jar;
2> CREATE FUNCTION html_decode FROM METHOD myapp.datatypes.Html.decode;

Note that the function name and method name do not have to be identical. Also, the function name is not case sensitive. However, the Java class and method names are case sensitive. Finally, the Java methods for user-defined functions must follow the same rules for determinism as user-defined stored procedures, as outlined in Section 5.1.2.2, “Avoid Introducing Non-deterministic Values from External Functions”.

Examples

The following example defines a function called emoticon from a Java method findEmojiCode:

CREATE FUNCTION emoticon FROM METHOD utils.Charcode.findEmojiCode;