Introduction to SQL UDFs

This topic covers concepts and usage details that are specific to SQL UDFs (user-defined functions).

For an introduction to UDFs, and for information that applies to all types of UDFs, see Overview of UDFs.

In this Topic:

General Usage

A SQL UDF evaluates an arbitrary SQL expression and returns the result(s) 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.

Security/Privilege Requirements for SQL 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;

-- This will fail because the role named "analyst" does not have the
-- privileges required in order to access the table named "users".
select * from users;

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

-- However, this will succeed.
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.