CREATE FUNCTION

Syntax

CREATE FUNCTION
    function_name (arg_name arg_type [, ...])
    RETURNS return_type
    LANGUAGE language_name
WITH (function_parameter = value [, ...]);

Description

Defines a new User-Defined Function (UDF), an executable routine defined in a supported language, which accepts an ordered list of input arguments, performs a computation, and generates a result value.

A Function’s signature has to be unique within an organization. The signature is a combination of the name, parameter types, and return type of the Function.

Currently, user-defined functions can only have arguments of the Primitive Data Types and generate result values of the Primitive Data Types.

Arguments

function_name

Name of the Function to create. For case-sensitive names, the name must be wrapped in double quotes, otherwise, the lowercased name will be used.

(arg_name arg_type [,…​])

An ordered list of Function argument names and types.

return_type

Data type for a Function’s result value.

language_name

Language in which the Function was created. Supports JAVA.

WITH (function_parameter = value [, …​ ])

This clause specifies the Function Parameters.

Function Parameters

Parameter Name	Description
source.name	Required. Specifies an existing Function Source to use for the content of the Function. See LIST FUNCTION_SOURCES. Type: String Valid values: A function_source that the user has access to. See LIST FUNCTION_SOURCES.
class.name	Required. Specifies the fully qualified class name defined within a Function Source. Type: String Valid values: A valid class name in the function_source.
egress.allow.uris	Optional. Specifies a comma delimited list of host:port endpoints that the function can send requests. This is only needed if a function needs to call a remote service.
## Example

Create user-defined Function with single input argument

The below DDL statement creates a new user-defined Function with the name toUpperCase. The Function accepts one argument of the data type VARCHAR and the data type of its return value is also VARCHAR. The Function's executable code is in a Java class with the name util.UpperCase available in the mysrc Function Source. Check CREATE FUNCTION SOURCE for details on how a Function Source can be added.

CREATE FUNCTION
    "toUpperCase" (s VARCHAR)
    RETURNS VARCHAR
    LANGUAGE JAVA
    WITH ( 'source.name' = 'mysrc', 'class.name' = 'util.UpperCase' );

You can use LIST FUNCTIONS command to get a list of created functions.

Create user-defined Function with multiple input arguments

The below DDL statement creates a new user-defined Function called getrate from a Java class with the name accounting.Exchange available in the finance Function Source. This Function receives two input arguments of data types: VARCHAR and BIGINT, and generates an output of the data type DECIMAL.

CREATE FUNCTION
    getrate (name VARCHAR, amount BIGINT)
    RETURNS DECIMAL
    LANGUAGE JAVA
    WITH ( 'source.name' = 'finance', 'class.name' = 'accounting.Exchange' );

Create user-defined Function with egress.allow.uris property

The below DDL statement creates a new user-defined Function called getrate from a Java class with the name accounting.Exchange available in the finance Function Source. This Function receives two input arguments of data types: VARCHAR and BIGINT, and generates an output of the data type DECIMAL.

This function will can also make remote calls to myhost1:9090 and myhost2:80. Without the egress.allow.uris property the function calls will be blocked.

CREATE FUNCTION
    getrate (name VARCHAR, amount BIGINT)
    RETURNS DECIMAL
    LANGUAGE JAVA
    WITH ( 'source.name' = 'finance', 'class.name' = 'accounting.Exchange', 'egress.allow.uris'='myhost1:9090;myhost2:80' );