Overview of User-Defined Functions

This topic covers concepts that apply to all user-defined functions.

In this Topic:

Note

All the CREATE FUNCTION examples in this topic illustrate the syntax for creating UDFs using SnowSQL or the Worksheet (in the Snowflake web interface). If these examples are executed in sfsql, you must include a forward slash character (/) on a new line after the CREATE FUNCTION command to terminate the command.

In SnowSQL and the Worksheet, a forward slash is not required and will return an error if specified.

Types of User-Defined Functions

SQL

A SQL UDF evaluates and returns an arbitrary SQL expression. The function definition must be a SQL expression that returns a single value. The expression may be a query expression, though for non-table functions, a 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 like 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 user-defined functions (UDFs) allow you to manipulate data using the JavaScript programming language and runtime. Functions are created similarly to SQL UDFs. Currently only scalar JavaScript UDFs are supported (i.e., JavaScript functions that accept multiple values and return a single value and represent an expression in SQL; we do not yet support table-generating or aggregate JavaScript UDFs).

JavaScript functions occupy the same namespace as all other functions (including SQL UDFs and built-in functions). JavaScript UDFs can not have the same name as built-in functions. Invoked functions are resolved according to the search path.

Table UDFs

A table function returns a set of rows, and can be accessed in the FROM clause of a query. A UDF is defined as a table function by specifying a return type containing the TABLE keyword. The return type specifies the names and types of the columns of the table. The function body of a table function must be a SELECT statement. The return types specified in the RETURNS clause must match the types of the expressions in the corresponding positions of the SELECT statement in the function body.

For example:

CREATE OR REPLACE FUNCTION get_countries_for_user ( id number )
  RETURNS TABLE (country_code char, country_name varchar)
  AS 'select distinct c.country_code, c.country_name
      from user_addresses a, countries c
      where a.user_id = id
      and c.country_code = a.country_code';

A table function is referenced in the FROM clause of a query, using the TABLE keyword. The columns defined by the table function can appear anywhere that a normal table column can be used.

select *
from table(get_countries_for_user(123)) cc
where cc.country_code in ('US','FR','CA');

--------------+---------------+
 COUNTRY_CODE | COUNTRY_NAME  |
--------------+---------------+
 FR           | France        |
 US           | United States |
--------------+---------------+

For more information about the system-defined table functions, see Table Functions.

Function Overloading

Function overloading is supported. Multiple functions in a schema can have the same name, as long as the argument signatures differ, either in the number of arguments or types of arguments. When an overloaded function is used, the correct function implementation is chosen based on the argument types in the function call. Consider the following examples, which create two 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 a function call to add5 has 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 of DATE to NUMBER is not supported.

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 Understanding Access Control in Snowflake.