Snowflake User Defined Functions, Syntax and Examples

An UDF evaluates an arbitrary SQL expression and returns the result(s) of the expression. Snowflake supports creating user defined function. You can create custom user-defined functions (UDF) using either SQL statements or Java script program. In this article, we will check Snowflake user defined functions, types, syntax, examples and how to create them.

Snowflake User Defined Functions

The custom user defined functions are bound to the database. But, fortunately, Snowflake supports cross database access. You can create custom user defined functions in one centralized database and access from all other databases. Once a function is created, anyone with required privileges can execute the function.

Types of User Defined Functions in Snowflake

Snowflake currently supports two types of UDFs, SQL and JavaScript.

SQL User Defined Functions
JavaScript User Defined Functions

Let us check these UDFs in brief with an example.

Snowflake SQL User Defined Function

An SQL UDF evaluates an arbitrary SQL expression and returns either scalar or tabular results.

Snowflake SQL UDF Syntax

Following is the SQL UDF syntax

create or replace function function_name(arument1 type, argument2 type,...)
  returns type
  as
  $$
    function_definition [SQL statements]
  $$
  ;

The function definition can be a SQL expression that returns either a scalar (i.e. single) value or, if defined as a table function, a set of rows

Snowflake SQL User Defined Function Example

Following Snowflake SQL UDF returns the largest of two.

create or replace function sql_udf_bignum (n1 float, n2 float)
 returns float
 as 
 $$
	select case when n1 > n2 then n1 else n2 end
 $$
 ;

You can include the created user defined function in your SQL statments.

For example,

select sql_udf_bignum(1,2) as largest_num;
+-------------+
| LARGEST_NUM |
|-------------|
|           2 |
+-------------+

select sql_udf_bignum(1.5,1.55) as largest_num;
+-------------+
| LARGEST_NUM |
|-------------|
|        1.55 |
+-------------+

Snowflake JavaScript User Defined Function

A JavaScript UDF lets you use the JavaScript programming language to manipulate data and return either scalar or tabular results. It will also allow you to handle errors using JavaScript try/catch block.

Snowflake Java Script UDF Syntax

Following is the Java Script UDF syntax

create or replace function function_name(arument1 type, argument2 type,...)
 returns type
 language javascript
 as
 $$
  function_definition [JavaScript statements]
 $$
;

Note that the JavaScript code must refer to the input parameter names as all upper-case, even if the names are not uppercase in the SQL code.

Snowflake JavaScript User Defined Function Example

Following Snowflake JavaScript UDF returns the largest of two.

create or replace function js_udf_bignum (n1 float, n2 float)
 returns float
 language javascript
 as 
 $$
	if (N1>N2) { return N1 } else { return N2}
 $$
 ;

Nested User Defined Functions in Snowflake

Snowflake supports nested function i.e. calling a function within another function. You can create the UDFs by calling other UDFs. Snowflake does not track the dependency while creating a UDF. The nested function must exist when you run the CREATE FUNCTION command.

 create or replace function nested_udf_test (n1 float, n2 float, n3 float, n4 float)
 returns float
 as 
 $$
select case when sql_udf_bignum(n1, n2) > sql_udf_bignum(n3, n4) then sql_udf_bignum(n1, n2) else sql_udf_bignum(n3, n4) end
 $$
 ;

For example, check the nested_udf_test function output.

select nested_udf_test(1,2,3,4) as largest_num;
+-------------+
| LARGEST_NUM |
|-------------|
|           4 |
+-------------+