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. For example, here is a basic example of a scalar UDF that calculates the area of a circle:
CREATE FUNCTION area_of_circle(radius FLOAT) RETURNS FLOAT AS $$ pi() * radius * radius $$ ;SELECT area_of_circle(1.0);Output:
+---------------------+ | AREA_OF_CIRCLE(1.0) | |---------------------| | 3.141592654 | +---------------------+
The expression can be a query expression (a SELECT expression). For example:
CREATE FUNCTION profit() RETURNS NUMERIC(11, 2) AS $$ SELECT SUM((retail_price - wholesale_price) * number_sold) FROM purchases $$ ;
When using a query expression in a SQL UDF, do not include a semicolon within the UDF body to terminate the query expression.
You can include only one query expression. The expression can include UNION [ALL].
Note
Although the body of a UDF can contain a complete SELECT statement, it cannot contain DDL statements or any DML statement other than SELECT.
Examples¶
Basic SQL Scalar UDF Example(s)¶
This example returns a hard-coded approximation of the mathematical constant pi.
CREATE FUNCTION pi_udf() RETURNS FLOAT AS '3.141592654::FLOAT' ;SELECT pi_udf();Output:
+-------------+ | PI_UDF() | |-------------| | 3.141592654 | +-------------+
Common SQL Examples¶
This example uses a query expression (i.e. a SELECT statement):
Create the table and data to use:
CREATE TABLE purchases (number_sold INTEGER, wholesale_price NUMBER(7,2), retail_price NUMBER(7,2)); INSERT INTO purchases (number_sold, wholesale_price, retail_price) VALUES (3, 10.00, 20.00), (5, 100.00, 200.00) ;Create the UDF:
CREATE FUNCTION profit() RETURNS NUMERIC(11, 2) AS $$ SELECT SUM((retail_price - wholesale_price) * number_sold) FROM purchases $$ ;Call the UDF in a query:
SELECT profit();Output:
+----------+ | PROFIT() | |----------| | 530.00 | +----------+
This example uses the UDF in a WITH clause:
CREATE TABLE circles (diameter FLOAT); INSERT INTO circles (diameter) VALUES (2.0), (4.0); CREATE FUNCTION diameter_to_radius(f FLOAT) RETURNS FLOAT AS $$ f / 2 $$ ;WITH radii AS (SELECT diameter_to_radius(diameter) AS radius FROM circles) SELECT radius FROM radii ORDER BY radius ;Output:
+--------+ | RADIUS | |--------| | 1 | | 2 | +--------+
This example uses a more complex query, which includes a JOIN operation:
Create the table and data to use:
CREATE TABLE orders (product_ID varchar, quantity integer, price numeric(11, 2), buyer_info varchar); CREATE TABLE inventory (product_ID varchar, quantity integer, price numeric(11, 2), vendor_info varchar); INSERT INTO inventory (product_ID, quantity, price, vendor_info) VALUES ('X24 Bicycle', 4, 1000.00, 'HelloVelo'), ('GreenStar Helmet', 8, 50.00, 'MellowVelo'), ('SoundFX', 5, 20.00, 'Annoying FX Corporation'); INSERT INTO orders (product_id, quantity, price, buyer_info) VALUES ('X24 Bicycle', 1, 1500.00, 'Jennifer Juniper'), ('GreenStar Helmet', 1, 75.00, 'Donovan Liege'), ('GreenStar Helmet', 1, 75.00, 'Montgomery Python');Create the UDF:
CREATE FUNCTION store_profit() RETURNS NUMERIC(11, 2) AS $$ SELECT SUM( (o.price - i.price) * o.quantity) FROM orders AS o, inventory AS i WHERE o.product_id = i.product_id $$ ;Call the UDF in a query:
SELECT store_profit();Output:
+----------------+ | STORE_PROFIT() | |----------------| | 550.00 | +----------------+
The topic CREATE FUNCTION contains additional examples.
Using UDFs in Different Clauses¶
A scalar UDF can be used any place a scalar expression can be used. For example:
-- ----- These examples show a UDF called from different clauses ----- -- select MyFunc(column1) from table1; select * from table1 where column2 > MyFunc(column1);
Using SQL Variables in a UDF¶
This example shows how to set a SQL variable and use that variable inside a UDF:
SET id_threshold = (SELECT COUNT(*) FROM table1) / 2;CREATE OR REPLACE FUNCTION my_filter_function() RETURNS TABLE (id int) AS $$ SELECT id FROM table1 WHERE id > $id_threshold $$ ;
Troubleshooting¶
Error Message: Unsupported subquery type
¶
- Cause
If a UDF contains a query expression, then the UDF can act as a subquery. If a subquery is passed a column name, then the subquery can act as a correlated subquery. If a correlated subquery violates the Snowflake rules for correlated subqueries, then the user gets the error message
Unsupported subquery type
. The example below shows an invalid correlated subquery, and how a UDF can act like a similar invalid correlated subquery.Create a pair of tables and load data into them:
CREATE TABLE stores (store_ID INTEGER, city VARCHAR); CREATE TABLE employees (employee_ID INTEGER, store_ID INTEGER, sales NUMERIC(10,2), sales_date DATE); INSERT INTO stores (store_ID, city) VALUES (1, 'Winnipeg'), (2, 'Toronto'); INSERT INTO employees (employee_ID, store_ID, sales, sales_date) VALUES (1001, 1, 9000.00, '2020-01-27'), (1002, 1, 2000.00, '2020-01-27'), (2001, 2, 6000.00, '2020-01-27'), (2002, 2, 4000.00, '2020-01-27'), (2002, 2, 5000.00, '2020-01-28') ;
The following SQL statement contains a correlated subquery that does not follow Snowflake rules. This code causes an
Unsupported subquery type
error:SELECT employee_ID, store_ID, (SELECT city FROM stores WHERE stores.store_ID = employees.store_ID) FROM employees;
The code below creates and then calls a subquery-like UDF in a way that creates a correlated subquery similar to the one shown above:
CREATE FUNCTION subquery_like_udf(X INT) RETURNS VARCHAR LANGUAGE SQL AS $$ SELECT city FROM stores WHERE stores.store_ID = X $$;
SELECT employee_ID, subquery_like_udf(employees.store_ID) FROM employees;
- Solution #1
If the UDF contains a query expression, then call the UDF only in ways consistent with the rules for subqueries.
For example, the following statement calls the UDF with a constant rather than with a column name, so the UDF does not act like a correlated subquery:
SELECT subquery_like_udf(1); +----------------------+ | SUBQUERY_LIKE_UDF(1) | |----------------------| | Winnipeg | +----------------------+
- Solution #2
In some cases, you can re-write the UDF to achieve the same goal a different way. A correlated subquery is allowed if the subquery can be statically determined to return one row. The following UDF uses an aggregate function and therefore returns only one row:
CREATE FUNCTION subquery_like_udf_2(X INT) RETURNS VARCHAR LANGUAGE SQL AS $$ SELECT ANY_VALUE(city) FROM stores WHERE stores.store_ID = X $$;
SELECT employee_ID, subquery_like_udf_2(employees.store_ID) FROM employees; +-------------+-----------------------------------------+ | EMPLOYEE_ID | SUBQUERY_LIKE_UDF_2(EMPLOYEES.STORE_ID) | |-------------+-----------------------------------------| | 1001 | Winnipeg | | 1002 | Winnipeg | | 2001 | Toronto | | 2002 | Toronto | | 2002 | Toronto | +-------------+-----------------------------------------+