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:

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.1, “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;