Skip to main content

CREATE FUNCTION Enterprise

Creates user-defined functions (UDFs) in the Arctic catalog. UDFs are SQL scalar or tabular functions that take a set of arguments and return a single value or a set of rows, respectively.

Syntax 
CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ]
    <function_name> ( [ <function_parameter> [, ...] ] )
    RETURNS { <data_type> | TABLE ( <column_name> [, ...] ) }
    RETURN { <expression> | <query> }

To run CREATE FUNCTION, users need the USAGE and COMMIT privileges on the Arctic catalog. In the following cases, users need additional privileges:

  • If the UDF doesn't exist in the catalog or folder, then the user needs the CREATE FUNCTION privilege on the catalog or folder where the UDF will be created.

  • If the UDF already exists in the catalog or folder, replacing the UDF only requires the WRITE privilege on the catalog or folder where the UDF exists.

Parameters​

[ OR REPLACE ] String   Optional

If specified, the UDF with the same name is replaced. You cannot specify this parameter with IF NOT EXISTS.

[ IF NOT EXISTS ] String   Optional

If specified, creates the UDF only when it does not exist. The creation of the UDF succeeds (no error results) if the specified UDF already exists in the system. You cannot specify this parameter with OR REPLACE. This is mainly useful to avoid trying to create the same UDF twice.

<function_name> String

The name of the UDF. UDF names within a project must be unique (no overloading), cannot conflict with system-defined functions, and are case-insensitive.

<function_parameter> String

Specifies a parameter of the UDF:

  • parameter_name - The parameter name must be unique within the UDF signature.
  • data_type - Any Dremio SQL data type, including primitive and complex types.

RETURNS <data_type> String

The return data type of a scalar function.

RETURNS TABLE ( <column_name> [, ...] ) String

The signature of the result of a tabular function:

  • column_name - The column name must be unique within the result signature.
  • data_type - Any Dremio SQL data type, including primitive and complex types.

A tabular UDF can be accessed only in the FROM clause of a query.

RETURN { <expression> | <query> } String

The body of the UDF. For a scalar function, it can either be a query or an expression. For a tabular function, it may only be a query. The expression may not contain the following:

  • DML statements such as INSERT, UPDATE, or DELETE.
  • DDL statements such as CREATE and DROP.

Examples​

Creating and Using Scalar Functions​

Create test data 
CREATE TABLE <catalog-name>.t (c1, c2) AS VALUES (0, 1), (1, 2);
Create a UDF with no parameters 
CREATE FUNCTION hello() RETURNS VARCHAR RETURN 'Hello World!';
SELECT hello();
-- Hello World!
Create a UDF that takes parameters 
CREATE FUNCTION area (x DOUBLE, y DOUBLE) RETURNS DOUBLE RETURN x * y;
Use a UDF in the SELECT clause of a query 
SELECT area (c1, c2) AS area FROM <catalog-name>.t;
-- 0.0
-- 2.0
Use a UDF in the WHERE clause of a query 
SELECT * FROM <catalog-name>.t WHERE area (c1, c2) > 0;
-- 1 2
Create a UDF 
CREATE FUNCTION IF NOT EXISTS greeting (name VARCHAR)
    RETURNS VARCHAR
    RETURN CONCAT ('Greetings and salutations, ', name);
SELECT greeting ('Dave');
-- Greetings and salutations, Dave

Creating and Using Tabular Functions​

Create test data 
CREATE TABLE <catalog-name>.t2 (name, color) AS VALUES ('strawberry', 'red'), ('banana', 'yellow');
Return a set of rows 
CREATE FUNCTION all_fruits()
    RETURNS TABLE (name VARCHAR, hue VARCHAR)
    RETURN SELECT * FROM <catalog-name>.t2;
SELECT * FROM TABLE (all_fruits());
-- strawberry  red
-- banana  yellow
Use an argument in a WHERE clause 
CREATE FUNCTION find_fruit (color VARCHAR)
    RETURNS TABLE (name VARCHAR)
    RETURN SELECT name FROM <catalog-name>.t2 WHERE <catalog-name>.t2.color = color;
SELECT * FROM TABLE (find_fruit ('red'));
-- strawberry
Filter the results of a UDF 
SELECT * FROM TABLE (all_fruits()) WHERE name LIKE '%berry';
-- strawberry