Skip to main content

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