Snowflake Supports Named and Optional Arguments
Snowflake now supports passing arguments by name and defining optional arguments in User-Defined Functions (UDFs), User-Defined Table Functions (UDTFs), and Stored Procedures.
Named Arguments
Using named arguments improves the clarity and readability of function and procedure calls. Consider the following UDF:
CREATE OR REPLACE FUNCTION add_numbers (n1 NUMBER, n2 NUMBER)
RETURNS NUMBER
AS 'n1 + n2';
Previously, arguments were required to be passed in the exact order they were defined. However, with this update, you can pass arguments by naming them, followed by =>, and then the argument’s value:
SELECT add_numbers(n1 => 10, n2 => 5);
Moreover, the arguments can be passed in any order:
SELECT add_numbers(n2 => 5, n1 => 10);
This feature is particularly useful for functions or procedures with a large number of parameters, which can be challenging to remember and manage.
Optional Arguments
In addition to named arguments, this update allows you to specify optional arguments for functions and procedures. By using the DEFAULT
keyword in the CREATE FUNCTION
or CREATE PROCEDURE
statements, you can now define default values for any argument, effectively making it optional. For example:
CREATE OR REPLACE FUNCTION build_string_udf(
word VARCHAR,
prefix VARCHAR DEFAULT 'pre-',
suffix VARCHAR DEFAULT '-post'
) ...
When invoking the UDF, it’s not mandatory to include all the optional arguments. You can choose to omit prefix
and suffix
, or to include only those you need to override:
SELECT build_string_udf(word => 'hello', suffix => '-after');
Note
It’s important to note that when overloading a function or procedure, optional arguments do not distinguish one signature from another. This also applies when using ALTER FUNCTION … RENAME
or ALTER PROCEDURE … RENAME
to rename a function or procedure. For more information on this new feature, please visit: Calling a UDF and Calling a Stored Procedure.