Overview of UDFs¶

This topic covers concepts and usage details that apply to all types of UDFs (user-defined functions).

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 can be a query expression, though for non-tabular 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 to database objects such as tables, views, and sequences. The UDF owner must have appropriate privileges on any database objects that the UDF accesses. A SQL UDF’s defining expression can 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 or a tabular result, depending on how the UDF is defined.

A JavaScript UDF’s defining expression can refer recursively to itself, but cannot refer to other user-defined functions.

Note

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, a UDF is scalar, returning at most one row, consisting of a single column/value.

However, a UDF can also be defined to return a set of rows with one or more columns, which can then be accessed in the FROM clause of a query. A user-defined table function (UDTF) is defined 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 UDTFs, including examples, see SQL UDTFs (User-Defined Table Functions) and JavaScript UDTFs (User-Defined Table Functions). For more general information about the system-defined table functions provided in Snowflake, see Table 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, for example:

SELECT temporary_db_qualified_names_test.temporary_schema_1.udf_pi();


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.

Snowflake supports overloading of SQL UDF names. Multiple SQL 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, Snowflake checks the arguments and calls the correct function.

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, because conversion from DATE to NUMBER is not supported.

For example:

select add5(1);

+---------+
|---------|
|       6 |
+---------+

+-----------+
|-----------|
| 15        |
+-----------+

+---------------+
|---------------|
| hello5        |
+---------------+

+-----------------------------+
|-----------------------------|
| 2014-01-015                 |
+-----------------------------+


Be careful when using overloading. The combination of automatic type conversion and overloading makes it easy to for a minor user error to cause unexpected results. For an example, see Overloading Function Names.

Caution

For UDFs written in languages other than SQL, the rules might differ.

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;

-- 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.

Examples¶

As described in Overloading of UDF Names (in this topic), you can overload function names.

The next example of overloading shows how combining overloading and automatic type conversion can make it easy to get unexpected results:

Create a function that takes a FLOAT parameter:

CREATE FUNCTION add_pi(PARAM_1 FLOAT)
RETURNS FLOAT
LANGUAGE SQL
AS $$PARAM_1 + 3.1415926::FLOAT$$;


Call the function twice. The first time, pass a FLOAT. The second time, pass a VARCHAR. The VARCHAR is converted to a FLOAT, and the output from each call is identical:

SELECT add_pi(1.0), add_pi('1.0');
+-------------+---------------+
|-------------+---------------|
|   4.1415926 |     4.1415926 |
+-------------+---------------+


Now, create an overloaded function that takes a VARCHAR parameter:

CREATE FUNCTION add_pi(PARAM_1 VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS $$PARAM_1 || ' + 3.1415926'$$;


Now use exact the same CALLs as before. Note the difference in output between these two CALLs and the previous two CALLs.

SELECT add_pi(1.0), add_pi('1.0');
+-------------+-----------------+