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 $scratch.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 $scratch.t;
-- 0.0
-- 2.0

Use a UDF in the WHERE clause of a query

SELECT * FROM $scratch.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 $scratch.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 $scratch.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 $scratch.t2 WHERE $scratch.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