Snowflake Scripting UDFs¶

Snowflake supports SQL user-defined functions (UDFs) that contain Snowflake Scripting procedural language. These UDFs are called Snowflake Scripting UDFs.

Snowflake Scripting UDFs can be called in a SQL statement, such as a SELECT statement or INSERT statement. Therefore, they are more flexible than a Snowflake Scripting stored procedure, which can only be called in a SQL CALL command.

General usage¶

A Snowflake Scripting UDF evaluates procedural code and returns a scalar (that is, single) value.

You can use the following subset of Snowflake Scripting syntax in Snowflake Scripting UDFs:

Supported data types¶

Snowflake Scripting UDFs support the following data types for both input arguments and return values:

Numeric data types (for example, INTEGER, NUMBER, and FLOAT)
String & binary data types (for example, VARCHAR and BINARY)
Date & time data types (for example, DATE, TIME, and TIMESTAMP)
Logical data types (for example, BOOLEAN)

Snowflake Scripting UDFs support the following data types for input arguments only:

Semi-structured data types (for example, VARIANT, OBJECT, and ARRAY)
Structured data types (for example, ARRAY, OBJECT, and MAP)

Limitations¶

The following limitations apply to Snowflake Scripting UDFs:

The following types of Snowflake Scripting syntax aren’t supported in Snowflake Scripting UDFs:
SQL statements aren’t supported in Snowflake Scripting UDFs (including SELECT, INSERT, UPDATE, and so on).
Snowflake Scripting UDFs can’t be defined as table functions.
The following expression types aren’t supported in Snowflake Scripting UDFs:
- User-defined functions
- Aggregation functions
- Window functions
Snowflake Scripting UDFs can’t be used when creating a materialized view.
Snowflake Scripting UDFs can’t be used when creating row access policies and masking policies.
Snowflake Scripting UDFs can’t be used to specify a default column value.
Snowflake Scripting UDFs can’t be used in a COPY INTO command for data loading and unloading.
Snowflake Scripting UDFs can’t be memoizable.
Snowflake Scripting UDFs have a limit of 500 input arguments.
You can’t log messages for Snowflake Scripting UDFs.

Examples¶

The following examples create and call Snowflake Scripting UDFs:

Create a Snowflake Scripting UDF with variables¶

Create a Snowflake Scripting UDF that calculates profit based on the values of two arguments:

CREATE OR REPLACE FUNCTION calculate_profit( cost NUMBER(38, 2), revenue NUMBER(38, 2)) RETURNS number(38, 2) LANGUAGE SQL AS DECLARE profit NUMBER(38, 2) DEFAULT 0.0; BEGIN profit := revenue - cost; RETURN profit; END; 

Copy

Note

If you use Snowflake CLI, SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, this example requires minor changes. For more information, see Using Snowflake Scripting in Snowflake CLI, SnowSQL, the Classic Console, and Python Connector.

Call calculate_profit in a query:

SELECT calculate_profit(100, 110); 

Copy

+----------------------------+ | CALCULATE_PROFIT(100, 110) | |----------------------------| | 10.00 | +----------------------------+ 

You can use the same Snowflake Scripting UDF and specify columns for the arguments. First, create a table and insert data:

CREATE OR REPLACE TABLE snowflake_scripting_udf_profit( cost NUMBER(38, 2), revenue NUMBER(38, 2)); INSERT INTO snowflake_scripting_udf_profit VALUES (100, 200), (200, 190), (300, 500), (400, 401); 

Copy

Call calculate_profit in a query and specify the columns for the arguments:

SELECT calculate_profit(cost, revenue) FROM snowflake_scripting_udf_profit; 

Copy

+---------------------------------+ | CALCULATE_PROFIT(COST, REVENUE) | |---------------------------------| | 100.00 | | -10.00 | | 200.00 | | 1.00 | +---------------------------------+ 

Create a Snowflake Scripting UDF with conditional logic¶

Create a Snowflake Scripting UDF that uses conditional logic to determine the department name based on an input INTEGER value:

CREATE OR REPLACE function check_dept(department_id INTEGER) RETURNS VARCHAR LANGUAGE SQL AS BEGIN IF (department_id < 3) THEN RETURN 'Engineering'; ELSEIF (department_id = 3) THEN RETURN 'Tool Design'; ELSE RETURN 'Marketing'; END IF; END; 

Copy

Note

Call check_dept in a query:

SELECT check_dept(2); 

Copy

+---------------+ | CHECK_DEPT(2) | |---------------| | Engineering | +---------------+ 

You can use a SQL variable in an argument when you call a Snowflake Scripting UDF. The following example sets a SQL variable and then uses the variable in a call to the check_dept UDF:

SET my_variable = 3; SELECT check_dept($my_variable); 

Copy

+--------------------------+ | CHECK_DEPT($MY_VARIABLE) | |--------------------------| | Tool Design | +--------------------------+ 

Create a Snowflake Scripting UDF with a loop¶

Create a Snowflake Scripting UDF that uses a loop to count all numbers up to a target number provided in an argument and calculate the sum of all of the numbers counted:

CREATE OR REPLACE function count_to( target_number INTEGER) RETURNS VARCHAR LANGUAGE SQL AS DECLARE counter INTEGER DEFAULT 0; sum_total INTEGER DEFAULT 0; BEGIN WHILE (counter < target_number) DO counter := counter + 1; sum_total := sum_total + counter; END WHILE; RETURN 'Counted to ' || counter || '. Sum of all numbers: ' || sum_total; END; 

Copy

Note

Call count_to in a query:

SELECT count_to(10); 

Copy

+---------------------------------------+ | COUNT_TO(10) | |---------------------------------------| | Counted to 10. Sum of all numbers: 55 | +---------------------------------------+ 

Create a Snowflake Scripting UDF with exception handling¶

Create a Snowflake Scripting UDF that declares an exception and then raises the exception:

CREATE OR REPLACE FUNCTION raise_exception(input_value INTEGER) RETURNS VARCHAR LANGUAGE SQL AS DECLARE counter_val INTEGER DEFAULT 0; my_exception EXCEPTION (-20002, 'My exception text'); BEGIN WHILE (counter_val < 12) DO counter_val := counter_val + 1; IF (counter_val > 10) THEN RAISE my_exception; END IF; END WHILE; RETURN counter_val; EXCEPTION WHEN my_exception THEN IF (input_value = 1) THEN RETURN 'My exception caught: ' || sqlcode; ELSEIF (input_value = 2) THEN RETURN 'My exception caught with different path: ' || sqlcode; END IF; RETURN 'Default exception handling path: ' || sqlcode; END; 

Copy

Note

Call raise_exception in a query and specify 1 for the input value:

SELECT raise_exception(1); 

Copy

+-----------------------------+ | RAISE_EXCEPTION(1) | |-----------------------------| | My exception caught: -20002 | +-----------------------------+ 

Call raise_exception in a query and specify 2 for the input value:

SELECT raise_exception(2); 

Copy

+-------------------------------------------------+ | RAISE_EXCEPTION(2) | |-------------------------------------------------| | My exception caught with different path: -20002 | +-------------------------------------------------+t 

Call raise_exception in a query and specify NULL for the input value:

SELECT raise_exception(NULL); 

Copy

+-----------------------------------------+ | RAISE_EXCEPTION(NULL) | |-----------------------------------------| | Default exception handling path: -20002 | +-----------------------------------------+ 

Create a Snowflake Scripting UDF that returns a value for an INSERT statement¶

Create a Snowflake Scripting UDF that returns a value that is used in an INSERT statement. Create the table that the values will be inserted into:

CREATE OR REPLACE TABLE test_sql_udf_insert (num NUMBER); 

Copy

Create a SQL UDF that returns a numeric value:

CREATE OR REPLACE FUNCTION value_to_insert(l NUMBER, r NUMBER) RETURNS number LANGUAGE SQL AS BEGIN IF (r < 0) THEN RETURN l/r * -1; ELSEIF (r > 0) THEN RETURN l/r; ELSE RETURN 0; END IF; END; 

Copy

Note

Call value_to_insert in multiple INSERT statements:

INSERT INTO test_sql_udf_insert SELECT value_to_insert(10, 2); INSERT INTO test_sql_udf_insert SELECT value_to_insert(10, -2); INSERT INTO test_sql_udf_insert SELECT value_to_insert(10, 0); 

Copy

Query the table to view the inserted values:

SELECT * FROM test_sql_udf_insert; 

Copy

+-----+ | NUM | |-----| | 5 | | 5 | | 0 | +-----+ 

Create a Snowflake Scripting UDF called in WHERE and ORDER BY clauses¶

Create a Snowflake Scripting UDF that returns a value that is used in a WHERE or ORDER BY clause. Create a table and insert values:

CREATE OR REPLACE TABLE test_sql_udf_clauses (p1 INT, p2 INT); INSERT INTO test_sql_udf_clauses VALUES (100, 7), (100, 3), (100, 4), (NULL, NULL); 

Copy

Create a SQL UDF that returns a numeric value that is the product of the multiplication of two input values:

CREATE OR REPLACE FUNCTION get_product(a INTEGER, b INTEGER) RETURNS VARCHAR LANGUAGE SQL AS BEGIN RETURN a * b; END; 

Copy

Note

Call get_product in the WHERE clause of a query to return the rows where the product is greater than 350:

SELECT * FROM test_sql_udf_clauses WHERE get_product(p1, p2) > 350; 

Copy

+-----+----+ | P1 | P2 | |-----+----| | 100 | 7 | | 100 | 4 | +-----+----+ 

Call get_product in the ORDER BY clause of a query to order the results from the lowest to the highest product returned by the UDF:

SELECT * FROM test_sql_udf_clauses ORDER BY get_product(p1, p2); 

Copy

+------+------+ | P1 | P2 | |------+------| | 100 | 3 | | 100 | 4 | | 100 | 7 | | NULL | NULL | +------+------+ 