CREATE FUNCTION

Creates a SQL scalar or tabular function that takes a set of arguments and returns 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_spec> [, ...] ) }
    RETURN { expression | query }

Parameters

[ OR REPLACE ] String   Optional

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

---

[ IF NOT EXISTS ] String   Optional

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

---

<function_name> String

The name of the function. Function 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 function:

• parameter_name - The parameter name must be unique within the function 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_spec> [, ...] ) 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 function. 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 function with no parameters

CREATE FUNCTION hello() RETURNS VARCHAR RETURN 'Hello World!';
SELECT hello();
-- Hello World!

Create a function that takes parameters

CREATE FUNCTION area (x DOUBLE, y DOUBLE) RETURNS DOUBLE RETURN x * y;

Use a function in the SELECT clause of a query

SELECT area (c1, c2) AS area FROM $scratch.t;
-- 0.0
-- 2.0

Use a function in the WHERE clause of a query

SELECT * FROM $scratch.t WHERE area (c1, c2) > 0;
-- 1 2

Create a function

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 function

SELECT * FROM TABLE (all_fruits()) WHERE name LIKE '%berry';
-- strawberry