Snowflake Supports Named and Optional Arguments

Photo by Sigmund on Unsplash

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.