Get startedGet started for free

User-Defined Functions (UDFs) - Part I

1. User-Defined Functions (UDFs) - Part I

Snowflake has a ton of built-in functions – think COALESCE, DATEDIFF, COUNT, etc. – but sometimes you need a custom function for a very specific use case. Maybe you have some SQL code you find yourself reusing a bunch, and it would be cleaner to just wrap it in a function. To make this easy, Snowflake lets you create user-defined functions, which I’ll usually refer to by their acronym, UDFs. When I first learned about UDFs, I was a little intimidated by them, and I’m not 100% sure why. I think it’s because they seemed fancy or complicated or something; I don’t really know. But I’ve come to understand that UDFs can be quite simple, and I hope you feel that way as well by the end of this video. Before we create and use our own UDFs, let’s run through a couple of examples of Snowflake’s pre-existing functions – so it’s *non*-user-defined functions. Let’s start with ABS(), which lets you take the absolute value of a number: SELECT ABS(-14); Pretty straightforward – If you put a negative number in there, it will flip the sign. If you put in a positive number, it will give you back the number you put in. Another common function is UPPER(), which takes a string and makes it uppercase: SELECT UPPER('upper'); Again, pretty straightforward. If you want to see a long list of functions provided by Snowflake, plus some others, just run SHOW FUNCTIONS. SHOW FUNCTIONS; If we scroll down, we can see the absolute-value function, and the columns give information about it – the number of arguments it takes, for example. Okay, let’s take an example from the world of Tasty Bytes. So what if you’re like – “Ugggh, all the time as part of other queries I’m running, I need to pull the price of the [highest-priced](https://quickstarts.snowflake.com/guide/getting_started_with_user_defined_sql_functions/index.html#3) item, so I keep having to write this subquery”: SELECT MAX(SALE_PRICE_USD) FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU; (The highest-priced item is $21 USD, but imagine that could change from time to time.) I just wish I had a function I could call that would return the max menu price at any given moment! Well, you’re in luck because it’s easy to create a user-defined function that does this. Here’s how you do it: You use the CREATE FUNCTION command, followed by the name of the new function you’re creating, and then you specify the data type for the output. An important thing to know about UDFs: They *always* return a scalar value. In the next video, we’ll learn about user-defined table functions (UDTFs), which return a table – so more than just a single scalar value – and in another video, we’ll learn about stored procedures, which are similar to UDFs and UDTFs in some ways, but they don’t have to return any value at all. Okay, so we’ve started creating our function – next we just bookend the code we want to run with $$. In our case, our code just selects a single number, and that’s what will get returned. I should note that we specify our database because a UDF is stored inside a database. If we don’t specify the schema, it will just use public. USE DATABASE FROSTBYTE_TASTY_BYTES; CREATE FUNCTION max_menu_price() RETURNS NUMBER(5,2) AS $$ SELECT MAX(SALE_PRICE_USD) FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU $$ ; Okay, if we scroll over to the left, we can see that function in the dropdown. So now let’s try out our UDF! Just run: SELECT max_menu_price(); And you’ll see it gives us our result of $21! Hooray! One cool thing is we should now be able to see our max_menu_price UDF when we run the SHOW FUNCTIONS command. Let’s run that to confirm. SHOW FUNCTIONS; It’s alphabetical, so if we scroll down, we’ll see it… and there it is! If you scroll over, you can see it’s… not built-in, it doesn’t take any arguments (max and min arguments are both 0), and its description is that it’s a user-defined function. I don’t know about you, but seeing our little function, all grown up and listed among the other powerful functions here, makes me feel very proud. Okay, so this is a very simple UDF, in that it doesn’t take in any arguments. In the next video, we’ll create one that does take in arguments. But don’t worry, dear little max_menu_price UDF, just because we’re making another UDF that’s more advanced, it doesn’t mean we don’t love you.

2. Let's practice!

Create Your Free Account

or

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