User-Defined Table Functions (UDTFs)

1. User-Defined Table Functions (UDTFs)

We’ve learned about user-defined functions, which are great, but have the limitation that you can only return a single value – no more, and no less. Now we’re going to learn about the elder sibling to UDFs – user-defined *table* functions (UDTFs), which return a table that can have multiple rows and multiple columns. Once you understand UDFs, UDTFs aren’t that much harder to grasp. Let’s work from the first UDF we made, and convert it into a UDTF so the output can be a bit more flexible. So that first UDF we made was this max_menu_price() function: CREATE FUNCTION max_menu_price() RETURNS NUMBER(5,2) AS $$ SELECT MAX(SALE_PRICE_USD) FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU $$ ; Now instead of just finding the max price, let’s find all menu items above a certain price that we specify when we call the function. We’ll call this UDTF menu_prices_above(). To change our earlier UDF to our new UDTF, we need to do four things: First, change the name – let’s make it menu_prices_above. Second, give it an argument – we’ll call this price_floor and make it a NUMBER. Third, change the content of the query so that it does the new thing we want it to do – Let’s have it pull both the price and the name of the item, and after selecting those two fields, it will filter down to only those rows where the SALE_PRICE_USD is greater than our price_floor. Fourth, have it return a table – So instead of just RETURNS, it will now say “RETURNS TABLE” and then we specify the names of the return values and their data types. The end result looks like this: CREATE FUNCTION menu_prices_above(price_floor NUMBER) RETURNS TABLE (item VARCHAR, price NUMBER) AS $$ SELECT MENU_ITEM_NAME, SALE_PRICE_USD FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU WHERE SALE_PRICE_USD > price_floor ORDER BY 2 DESC $$ ; Of everything I said just now, the most important thing to remember is that when you’re making a UDTF, you need to specify “RETURNS TABLE” and then put the return column names and types in parentheses, separated by commas. If you do that, you’ll probably be all right. So let’s double check that we created this as we’d hoped by running SHOW FUNCTIONS like we did in the last video: SHOW FUNCTIONS; If we scroll down, we can see menu_prices_above, and if we scroll to the right, we can see that the column is_table_function has a Y in it. Great! We made a UDTF! Now let’s run this. Running UDTFs is a little bit different from running UDFs. With a UDF, we just did SELECT function(), and we were all set. With UDTFs, we put the UDTF in the FROM statement, and we wrap the UDTF call in the table function TABLE(): SELECT * FROM TABLE(menu_prices_above(15)); The Snowflake docs state that having this TABLE function “[help](https://docs.snowflake.com/en/sql-reference/functions-table#using-a-table-function-in-the-from-clause)[s] the SQL compiler recognize a table function as a source of rows.” So basically we’re like: “Hey query compiler, just so you know, menu_prices_above isn’t going to just return a single value! It could return one row, ten rows, 100 rows. Be ready!” When we run this, we get back all menu items with a price above $15. Pretty cool! You could then further filter your results, if you wanted, by adding a WHERE clause on top of all of this – for example, we could specify that we only want menu items with the word “chicken” in the name: SELECT * FROM TABLE(menu_prices_above(15)) WHERE ITEM ILIKE '%CHICKEN%'; UDTFs are pretty cool. The world is your oyster. And as with UDFs, you can have SQL, Python, Java, and Javascript UDTFs – the one language difference is that you can’t have Scala UDTFs. All right, that’s all the user-defined table function material we’re going to cover for now! In this video we learned how to create UDTFs, and how to run them. Let’s move on to stored procedures, which are different from UDFs and UDTFs, but conceptually related, and for certain problems, you’ll end up choosing between using a UDF / UDTF and using a stored procedure, so it’s good to tackle them all in a row.

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.