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
Examples
Troubleshooting
- Error Message: Unsupported subquery type

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_2(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                                 |
+-------------+-----------------------------------------+

SQL UDFs¶

General Usage¶

Examples¶

Basic SQL Scalar UDF Example(s)¶

Common SQL Examples¶

Using UDFs in Different Clauses¶

Using SQL Variables in a UDF¶

Troubleshooting¶

Error Message: Unsupported subquery type¶

Error Message: `Unsupported subquery type`¶