Dremio Cloud
Dremio Software
 
On this page

    CREATE FUNCTION

    Create a function with or without parameters.

    Syntax 
    CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ]
    <function_name> ( [ <function_parameter> [, ...] ] )
    RETURNS { <data_type> }
    RETURN { <query> }

    Parameters

    [ OR REPLACE ]

    String

    Optional

    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

    Optional

    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). The parameter list can be empty, but the parentheses still have to be there. 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 a function with no parameters 
    CREATE FUNCTION hello()
RETURNS VARCHAR RETURN SELECT 'Hello World!'
    Create a function that takes parameters 
    CREATE FUNCTION multiply (x INT, y INT)
RETURNS INT RETURN SELECT x * y
    Create a function that serves as a column-masking policy 
    CREATE FUNCTION protect_ssn (val VARCHAR) RETURNS VARCHAR RETURN
	SELECT
    	CASE
            WHEN query_user() IN ('dave', 'mike') THEN val
            ELSE CONCAT('XXX-XX-', SUBSTR(val, 8, 4))
        END
    Create a function that serves as a row-access policy 
    CREATE FUNCTION hide_undercover (val VARCHAR) RETURNS BOOLEAN RETURN
	SELECT
		CASE
        	WHEN is_member('chief') or is_member('captain') THEN TRUE
            WHEN val = 'undercover' THEN FALSE
            ELSE TRUE
        END