SQL UDTFs (User-Defined Table Functions)

A UDTF returns a set of rows instead of a single, scalar value, and can be accessed in the FROM clause of a query. Snowflake supports both SQL and JavaScript UDTFs. This topic covers SQL UDTFs.

For information about JavaScript UDTFs, see JavaScript UDTFs (User-Defined Table Functions).

In this Topic:

Syntax

CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
  RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
  AS '<sql_expression>'

For a more detailed description of the general syntax for all UDFs, including SQL UDTFs, see CREATE FUNCTION.

Arguments

name:

This should be valid database object name that follows the rules described at: Identifier Requirements.

arguments:

This must be an expression, for example a column name, a literal, or an expression that can be evaluated to a single value. Typically, a function takes one argument, which is a column name. You can pass more than one value, for example, more than one column name, or a column name and one or more literal values.

It is possible to pass a constant or no value at all. However, in most cases, if the input is the same every time, then the output is the same every time.

output_col_name:

The name of the output column. There must be at least one output column.

output_col_type:

The data type of the output column.

sql_expression:

This must be a valid SQL expression or statement that returns a table (i.e. zero or more rows, each of which has one or more columns). The outputs must match the number and data types specified in the RETURNS clause.

Usage Notes

  • The main body (aka “definition”) of a SQL UDTF must be a SELECT expression.

  • Although the delimiters around the sql_expression are typically single quotes, you can use a pair of dollar signs $$ as the delimiter. The closing delimiter must match the opening delimiter. A pair of dollar signs is convenient when the sql_expression contains single quotes. An example using a pair of dollar signs is included in the Examples section below.

  • If the delimiter is a single quote, and the body contains a single quote, you can escape the single quote in the body by using the backslash character \ as the escape character. An example is included in the Examples section below.

  • A SQL UDTF is typically called in the FROM clause of a query, using the TABLE keyword. The columns defined in the UDTF can appear anywhere that a normal table column can be used.

  • The return types specified in the RETURNS clause determine the names and types of the columns in the tabular results and must match the types of the expressions in the corresponding positions of the SELECT statement in the function body.

Sample SQL UDTFs

Basic Examples

This is an artificially simple example of a UDTF, which hard-codes the output. This also illustrates the use of $$ as a delimiter:

CREATE FUNCTION t()
    RETURNS TABLE(msg VARCHAR)
    AS
    $$
        SELECT 'Hello'
        UNION
        SELECT 'World'
    $$;
SELECT msg 
    FROM TABLE(t())
    ORDER BY msg;
+-------+
| MSG   |
|-------|
| Hello |
| World |
+-------+

This example is similar to the preceding example, but it uses single quotes as the delimiter, and uses the \ escape character to escape the single quotes in the body of the UDTF:

CREATE FUNCTION t()
    RETURNS TABLE(msg VARCHAR)
    AS
    '
        SELECT \'Hello\'
        UNION
        SELECT \'World\'
    ';
SELECT msg
    FROM TABLE(t())
    ORDER BY msg;
+-------+
| MSG   |
|-------|
| Hello |
| World |
+-------+

This is another basic example of a UDTF. It queries a table and returns two of the columns from that table:

create or replace table orders (
    product_id varchar, 
    quantity_sold numeric(11, 2)
    );

insert into orders (product_id, quantity_sold) values 
    ('compostable bags', 2000),
    ('re-usable cups',  1000);
create or replace function orders_for_product(PROD_ID varchar)
    returns table (Product_ID varchar, Quantity_Sold numeric(11, 2))
    as
    $$
        select product_ID, quantity_sold 
            from orders 
            where product_ID = PROD_ID
    $$
    ;
select product_id, quantity_sold
    from table(orders_for_product('compostable bags'))
    order by product_id;
+------------------+---------------+
| PRODUCT_ID       | QUANTITY_SOLD |
|------------------+---------------|
| compostable bags |       2000.00 |
+------------------+---------------+

This same functionality can also be implemented using a view.

Examples with Joins

Create and use a SQL UDTF that returns country information (COUNTRY_CODE and COUNTRY_NAME) for a specified user ID:

create or replace table countries (country_code char(2), country_name varchar);
insert into countries (country_code, country_name) values 
    ('FR', 'FRANCE'),
    ('US', 'UNITED STATES'),
    ('SP', 'SPAIN');

create or replace table user_addresses (user_ID integer, country_code char(2));
insert into user_addresses (user_id, country_code) values 
    (100, 'SP'),
    (123, 'FR'),
    (123, 'US');
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';
select *
    from table(get_countries_for_user(123)) cc
    where cc.country_code in ('US','FR','CA')
    order by country_code;
+--------------+---------------+
| COUNTRY_CODE | COUNTRY_NAME  |
|--------------+---------------|
| FR           | FRANCE        |
| US           | UNITED STATES |
+--------------+---------------+

Create a SQL UDTF that returns the favorite color for a specified year:

create or replace table favorite_years as
    select 2016 year
    UNION ALL
    select 2017
    UNION ALL
    select 2018
    UNION ALL
    select 2019;

 create or replace table colors as
    select 2017 year, 'red' color, true favorite
    UNION ALL
    select 2017 year, 'orange' color, true favorite
    UNION ALL
    select 2017 year, 'green' color, false favorite
    UNION ALL
    select 2018 year, 'blue' color, true favorite
    UNION ALL
    select 2018 year, 'violet' color, true favorite
    UNION ALL
    select 2018 year, 'brown' color, false favorite;

create or replace table fashion as
    select 2017 year, 'red' fashion_color
    UNION ALL
    select 2018 year, 'black' fashion_color
    UNION ALL
    select 2019 year, 'orange' fashion_color;
create or replace function favorite_colors(the_year int)
    returns table(color string) as
    'select color from colors where year=the_year and favorite=true';

Use the UDTF in a query:

select * from table(favorite_colors(2017));
+--------+
| COLOR  |
|--------|
| red    |
| orange |
+--------+

Use the UDTF in a join with another table; note that the join column from the table is passed as an argument to the function.

select * 
    from favorite_years y join table(favorite_colors(y.year)) c;
+------+--------+
| YEAR | COLOR  |
|------+--------|
| 2017 | red    |
| 2017 | orange |
| 2018 | blue   |
| 2018 | violet |
+------+--------+

Use a WHERE clause, rather than ON, for additional predicates:

select * 
    from fashion f join table(favorite_colors(f.year)) fav
    where fav.color = f.fashion_color ;
+------+---------------+-------+
| YEAR | FASHION_COLOR | COLOR |
|------+---------------+-------|
| 2017 | red           | red   |
+------+---------------+-------+

Use the UDTF with a constant in a join expression; note that a WHERE clause, rather than ON, must be used for additional join conditions:

select fav.color as favorite_2017, f.*
    from fashion f JOIN table(favorite_colors(2017)) fav
    where fav.color = f.fashion_color;
+---------------+------+---------------+
| FAVORITE_2017 | YEAR | FASHION_COLOR |
|---------------+------+---------------|
| red           | 2017 | red           |
| orange        | 2019 | orange        |
+---------------+------+---------------+