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.
SyntaxCREATE [ 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
, orDELETE
. - DDL statements such as
CREATE
andDROP
.
Examples
Creating and Using Scalar Functions
Create test dataCREATE TABLE $scratch.t (c1, c2) AS VALUES (0, 1), (1, 2);
CREATE FUNCTION hello() RETURNS VARCHAR RETURN 'Hello World!';
SELECT hello();
-- Hello World!
CREATE FUNCTION area (x DOUBLE, y DOUBLE) RETURNS DOUBLE RETURN x * y;
SELECT area (c1, c2) AS area FROM $scratch.t;
-- 0.0
-- 2.0
SELECT * FROM $scratch.t WHERE area (c1, c2) > 0;
-- 1 2
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 dataCREATE TABLE $scratch.t2 (name, color) AS VALUES ('strawberry', 'red'), ('banana', 'yellow');
CREATE FUNCTION all_fruits()
RETURNS TABLE (name VARCHAR, hue VARCHAR)
RETURN SELECT * FROM $scratch.t2;
SELECT * FROM TABLE (all_fruits());
-- strawberry red
-- banana yellow
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
SELECT * FROM TABLE (all_fruits()) WHERE name LIKE '%berry';
-- strawberry