On this page

    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

    Syntax
    SHOW FUNCTIONS [ LIKE { pattern } ]
    

    Parameters

    [ LIKE ]

    Optional

    The pattern match is case-insensitive, but must be used with the LIKE operator.

    Examples

    Show all existing functions
    SHOW FUNCTIONS;
    
    Show an existing function with the exact name protect_ssn
    SHOW FUNCTIONS LIKE 'protect_ssn';
    
    Show all existing functions with names that start with protect_
    SHOW FUNCTIONS LIKE 'protect_%';
    

    Creating a Function

    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;
    

    Describing a Function

    Syntax
    { DESC | DESCRIBE } FUNCTION function_name;
    

    Parameters

    <function_name>

    String

    The name of an existing user-defined function.

    Example

    Display the metadata associated with an existing function
    DESCRIBE FUNCTION protect_ssn;
    

    Dropping a Function

    Only the owner of a UDF may drop it.

    Syntax
    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

    Remove an existing function with the exact name redact_ssn
    DROP FUNCTION redact_ssn;
    
    Remove an existing function with the exact name redact_ssn, but without exceptions if it doesn't exist
    DROP FUNCTION IF EXISTS redact_ssn;