Define default value to arguments in Snowflake Function (UDF)

Question

I want to create SQL function two params:

CREATE OR REPLACE FUNCTION myudf (varchar_argument VARCHAR, number_argument NUMBER) ...

But I want to set default value if the user did not specify all arguments while calling select * from (table(myudf('varchar_argument'))

I saw the option to create two functions with the same name but different arguments amount, but I dont want to duplicate my code since maintenance can be tricky.

any other option?

put default value in function creation

Answer 1

As a time of writing UDF default arguments are not supported.

Option 1: Overloading functions

Option 2: Passing second argument as NULL and handle it inside UDF

select * 
from (table(myudf('varchar_argument', NULL::NUMBER));

Function:

CREATE OR REPLACE FUNCTION myudf (varchar_argument VARCHAR, number_argument NUMBER) 
...
AS
SELECT *
FROM ...
WHERE col = varchar_argument
  AND col2 = COALESCE(number_argument , <default_value_here>);

---

EDIT:

Option 3: UDF's argument DEFAULT value:

CREATE OR REPLACE FUNCTION myudf (varchar_argument VARCHAR,
                                  number_argument NUMBER DEFAULT 10) 
...
AS
SELECT *
FROM ...
WHERE col = varchar_argument
  AND col2 = number_argument ;

Invocation:

select * 
from (table(myudf(varchar_argument => 'some_value'));