Introduction to User-Defined Functions (UDFs)

This topic covers concepts and usage details that apply to all types of UDFs.

In this Topic:

Types of User-Defined Functions

SQL

A SQL UDF evaluates an arbitrary SQL expression and returns the results of the expression. 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.

The expression may be a query expression, though for non-table functions, the query expression must be guaranteed to return at most one row, containing a single column. The expression defining a UDF can refer to the input arguments of the function, and database objects such as tables, views, and sequences, that are accessible to the owner of the function. A function’s defining expression may refer to other user-defined functions, though it cannot refer recursively to itself, either directly or through another function calling back to it.

JavaScript

JavaScript UDFs allow you to manipulate data using the JavaScript programming language and runtime environment. JavaScript UDFs are created in the same way as SQL UDFs, but with the LANGUAGE parameter set to JAVASCRIPT.

Similar to SQL UDFs, JavaScript UDFs can return either a scalar result or result as a table, depending on how the UDF is defined.

JavaScript UDFs have some requirements, usage details, and limitations that don’t apply to SQL UDFs. For more details, including examples, see JavaScript UDFs.

Scalar vs Table UDFs

By default, UDFs are scalar, returning at most one row, consisting of a single column/value.

However, UDFs can also be defined as table functions that return a set of rows, which can then be accessed in the FROM clause of a query. A UDF is defined as a table function by specifying a return type that contains the TABLE keyword and specifies the names and types of the columns in the table results.

For more details about creating and using table UDFs, including examples, see SQL and JavaScript Table UDFs. For more general information about table functions in Snowflake, see Table Literals / Functions.

Naming Conventions for UDFs

UDFs are database objects, meaning that they are created in a specified database and schema. As such, they have a fully-qualified name defined by their namespace, in the form of <db>.<schema>.<function_name>. When called without their fully-qualified name, UDFs are resolved according to the database and schema in use for the session.

This is in contrast to the built-in, system-defined functions provided by Snowflake, which have no namespace and, therefore, can be called from anywhere.

Conflicts with System-defined Functions

To avoid conflicts when calling functions, Snowflake does not allow creating UDFs with the same name as any of the system-defined functions.

Overloading of UDF Names

Snowflake supports overloading of UDF names, i.e. multiple UDFs in the same schema can have the same name, as long as their argument signatures differ, either by the number of arguments or the argument types. When an overloaded UDF is called, the function implementation used to execute the function is determined by the argument types in the function call.

Consider the following examples, which create two SQL UDFs named add5:

CREATE OR REPLACE FUNCTION add5 (n number)
  RETURNS number
  AS 'n + 5';

CREATE OR REPLACE FUNCTION add5 (s string)
  RETURNS string
  AS 's || ''5''';

Important

In the second ADD5 function, single quotes are used to escape the string literal '5'. Any single quotes used in a UDF definition must be escaped by single quotes.

If add5 is called with a numeric argument, then the first implementation is chosen, while a string-typed argument uses the second implementation. If the argument is neither a number nor a string, then the implementation depends on Snowflake’s implicit type conversion rules. For instance, a date-typed argument would be converted to a string, and the string implementation would be selected, since conversion from DATE to NUMBER is not supported.

For example:

select add5(1);

+---------+
| ADD5(1) |
|---------|
|       6 |
+---------+

select add5('1');

+-----------+
| ADD5('1') |
|-----------|
| 15        |
+-----------+

select add5('hello');

+---------------+
| ADD5('HELLO') |
|---------------|
| hello5        |
+---------------+

select add5(to_date('2014-01-01'));

+-----------------------------+
| ADD5(TO_DATE('2014-01-01')) |
|-----------------------------|
| 2014-01-015                 |
+-----------------------------+

Security/Privilege Requirements for UDFs

If a function definition refers to an unqualified table, then that table is resolved in the schema containing the function. A reference to another schema object (e.g. table, view, or other function) requires that the owner of the function has privileges to access that schema object. The invoker of the function need not have access to the objects referenced in the function definition, but only needs the privilege to use the function.

For example, an administrator owns a table named users, which contains sensitive data that is not generally accessible, but the administrator can expose the total user count through a function which other users have access privileges on:

use role dataadmin;

desc table users;

+-----------+--------------+--------+-------+---------+-------------+------------+--------+------------+---------+
| name      | type         | kind   | null? | default | primary key | unique key | check  | expression | comment |
|-----------+--------------+--------+-------+---------+-------------+------------+--------+------------+---------|
| USER_ID   | NUMBER(38,0) | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
| USER_NAME | VARCHAR(100) | COLUMN | Y     | [NULL]  | N           | N          | [NULL] | [NULL]     | [NULL]  |
  ...
  ...
  ...
+-----------+--------------+--------+-------+---------+-------------+------------+--------+------------+---------+

create function total_user_count() returns number as 'select count(*) from users';

grant usage on function total_user_count() to role analyst;

use role analyst;
select * from users;

FAILURE: SQL compilation error:
Object 'USERS' does not exist.

select total_user_count();

+--------------------+
| TOTAL_USER_COUNT() |
|--------------------+
| 123                |
+--------------------+

For more information about using roles and privileges to manage access control, see Access Control in Snowflake.