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_columns> )
  AS '<sql_expression>';

Note:

  • The function body of a SQL UDTF must be a SELECT expression.
  • 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.

Examples

Here is a very simple example of a UDTF. This example queries a table and returns two of the columns from that table. (This could also have been done with a view.)

create or replace function orders_for_product(PROD_ID varchar)
  returns table (Product_ID varchar, QuantitySold numeric(11, 2))
  as
  $$
    select product_ID, quantity from orders where product_ID = PROD_ID
  $$
  ;
select * from table(orders_for_product('GreenStar Helmet'));

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

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';

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

-- Set up tables for example
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;

-- UDTF that lists favorite colors for a given year
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';

Calling SQL UDTFs in Queries

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.

For example:

  • Using the get_countries_for_user UDTF created in Examples (in this topic), query the country information for the user with ID 123:

    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 |
    +--------------+---------------+
    
  • Using the favorite_colors UDTF created in Examples (in this topic), query the country information for the user with ID 123:

    -- Return the list of favorite colors for 2017
    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        |
    +---------------+------+---------------+