argumentssnowflake-cloud-data-platformuser-defined-functionsdefault-value

Define default value to arguments in Snowflake Function (UDF)


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


Solution

  • 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'));