Learning Snowflake SQL and Scripting

Chapter 17. Table Functions

This chapter explores the table function, which offers a flexible way to provide access to your Snowflake data.

User-Defined Functions

In Chapter 16, you learned how to create stored procedures using Snowflake Scripting, and I pointed out that even though stored procedures can return result sets, they can’t be called from SQL statements. Fortunately, Snowflake also allows you to create user-defined functions using the create function statement.  Also known as UDFs, user-defined functions can be called from SQL statements, but unlike stored procedures they are limited to returning a single expression and cannot take full advantage of the Snowflake Scripting language. UDFs can be written in Python, Java, JavaScript, Scala, and SQL, and this chapter will focus on SQL-based UDFs.

To start, I’ll take the simple stored procedure rectangle_area(), which was the focus of one of the exercises in Chapter 16, and turn it into a stored function.  Here’s the stored procedure:

create procedure rectangle_area(p_width number, p_length number)
returns number
language sql
as
begin
  return p_width * p_length;
end;

This stored procedure simply returns the product of two numeric parameters. Here’s what it looks like as a UDF named fn_rectangle_area():

create function fn_rectangle_area(p_width number, p_length number)
returns number
language sql
as
'p_width * p_length';

The function specifies an expression, which in this case is p_width * p_length. There is no looping, ...