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.

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

-- Set up tables for examplecreateorreplacetablefavorite_yearsasselect2016yearUNIONALLselect2017UNIONALLselect2018UNIONALLselect2019;createorreplacetablecolorsasselect2017year,'red'color,truefavoriteUNIONALLselect2017year,'orange'color,truefavoriteUNIONALLselect2017year,'green'color,falsefavoriteUNIONALLselect2018year,'blue'color,truefavoriteUNIONALLselect2018year,'violet'color,truefavoriteUNIONALLselect2018year,'brown'color,falsefavorite;createorreplacetablefashionasselect2017year,'red'fashion_colorUNIONALLselect2018year,'black'fashion_colorUNIONALLselect2019year,'orange'fashion_color;-- UDTF that lists favorite colors for a given yearcreateorreplacefunctionfavorite_colors(the_yearint)returnstable(colorstring)as'select color from colors where year=the_year and favorite=true';

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 2017select*fromtable(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 functionselect*fromfavorite_yearsyjointable(favorite_colors(y.year))c;+------+--------+| YEAR | COLOR ||------+--------|| 2017 | red || 2017 | orange || 2018 | blue || 2018 | violet |+------+--------+-- Use a WHERE clause, rather than ON, for additional predicatesselect*fromfashionfjointable(favorite_colors(f.year))favwherefav.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 conditionsselectfav.colorasfavorite_2017,f.*fromfashionfJOINtable(favorite_colors(2017))favwherefav.color=f.fashion_color;+---------------+------+---------------+| FAVORITE_2017 | YEAR | FASHION_COLOR ||---------------+------+---------------|| red | 2017 | red || orange | 2019 | orange |+---------------+------+---------------+