Introduction to SQL UDFs

You can write the handler for a user-defined function (UDF) in SQL. Topics in this section describe how to design and write a SQL handler. You’ll also find examples.

For an introduction to UDFs, including a list of languages in which you can write a UDF handler, refer to User-defined functions overview.

Once you have a handler, you create the UDF with SQL. For information on using SQL to create or call a UDF, refer to Creating a user-defined function or Executing a UDF.

Note

For limitations related to SQL UDF handlers, refer to SQL UDF limitations.

How a SQL handler works

When a user calls a UDF, the user passes UDF’s name and arguments to Snowflake. Snowflake calls the associated handler code (with arguments, if any) to execute the UDF’s logic. The handler method then returns the output to Snowflake, which passes it back to the client.

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.

Example

Code in the following example creates a UDF called area_of_circle containing handler code that calculates a circle’s area from the radius value received by the UDF as an argument.

CREATE FUNCTION area_of_circle(radius FLOAT)
  RETURNS FLOAT
  AS
  $$
    pi() * radius * radius
  $$
  ;
Copy

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;
Copy
+-----------+--------------+--------+-------+---------+-------------+------------+--------+------------+---------+
| 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;
Copy
FAILURE: SQL compilation error:
Object 'USERS' does not exist.

-- However, this will succeed.
SELECT total_user_count();
Copy
+--------------------+
| TOTAL_USER_COUNT() |
|--------------------+
| 123                |
+--------------------+

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

Language: English