User-Defined Functions
A user-defined function (UDF) is a callable routine that accepts input parameters, executes the function body, and returns a single scalar value. UDFs are used with Dremio in the context of assigning row-level filtering and column-masking policies.
A UDF can serve as a policy on multiple objects, which means you can change the behavior in one place and have the change reflected in multiple places. You may likewise replace or drop a UDF that is being used as a policy, but if you drop the UDF without replacement, the table or view will become unreadable until you remove the policy or create a new UDF for the policy.
To run a UDF, the user must have the EXECUTE privilege on that function.
Showing a Function
SHOW FUNCTIONS [ LIKE { pattern } ]
Parameters
pattern
String
A pattern used to filter the results of the statement. Any leading and trailing blanks are trimmed in the input pattern before processing. The pattern match is case-insensitive, but must be used with the LIKE operator.
Examples
SHOW FUNCTIONS;
SHOW FUNCTIONS LIKE 'protect_ssn';
SHOW FUNCTIONS LIKE 'protect_%';
Creating a Function
CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ]
function_name ( [ function_parameter [, ...] ] )
RETURNS { data_type }
RETURN { query }
Parameters
OR REPLACE
String
If specified, any function with the same name and signature (i.e., the same number of parameters and parameter types) will be replaced. You cannot replace an existing function with a different signature. This is primarily used to update a function body or the return type of the function. You cannot specify this parameter with the IF NOT EXISTS qualifier.
IF NOT EXISTS
String
When specified, this creates a function only if one does not exist. The creation of the function succeeds (without any errors) only if the specified function does not already exist in the system. You cannot specify this parameter with OR REPLACE.
function_name
String
The name of the function. Function names within a project must be unique, cannot conflict with system-defined functions, and are case-insensitive.
function_parameter
String
Specifies a parameter of the function by parameter name and data type (e.g., parameter_name data_type). Any supported Dremio SQL data type may be used, including primitive and complex types.
RETURNS data_type
String
The return data type of a scalar function.
query
String
The body of the function. The query may not contain the following: aggregate functions such as COUNT or SUM; window functions such as LAG or LEAD; ranking functions such as RANK or ROW_NUMBER; row-producing functions such as FLATTEN; DML statements such as INSERT, UPDATE, or DELETE; and DDL statements such as CREATE or DROP. Within the body of the function you can refer to a parameter by its unqualified name or by qualifying the parameter with the function name.
Examples
CREATE FUNCTION hello() RETURNS VARCHAR RETURN SELECT 'Hello World!';
CREATE FUNCTION multiply (x INT, y INT) RETURNS INT RETURN SELECT x * y;
Describing a Function
{ DESC | DESCRIBE } FUNCTION function_name;
Parameters
function_name
String
The name of an existing user-defined function.
Example
DESCRIBE FUNCTION protect_ssn;
Dropping a Function
Only the owner of a UDF may drop it.
DROP FUNCTION [ IF EXISTS ] function_name
Parameters
IF EXISTS
String
Optional
If included in a query, this phrase prevents exceptions from being thrown should the specified function not exist.
function_name
String
The name of an existing user-defined function.
Example
DROP FUNCTION redact_ssn;
DROP FUNCTION IF EXISTS redact_ssn;