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

{{< sql-section file="data/sql/functions.json" data="creatingFunctionsParameters" >}}

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