CREATE FUNCTION

Note Only valid for approved organizations. Please contact us to enable functions.

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.

The name and parameter types of a function’s signature must be unique within an organization. The signature is a combination of the name, parameter types, and return type of the function.

See the Creating a Function tutorial for a full example of adding a function.

Arguments

function_name

Name of the function to create. If the name is case-sensitive, you must wrap it in double quotes; otherwise, the system uses the lower case name.

(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 to which the user has access. 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; 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 to add a function source.

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 are 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' );