Snowflake UDF with variable number of inputs-CodePudding

I want to pass a variable number of inputs to the following udf in Snowflake.

CREATE FUNCTION concat_ws_athena(s1 string, s2 string)
  returns string
  as 
  $$
  array_to_string(array_construct_compact(s1, s2), '')
  $$
  ;

How do you declare variable number of inputs?

Simply using an array does not work:

CREATE FUNCTION concat_ws_athena(s array)
  returns string
  as 
  $$
  array_to_string(array_construct_compact(s), '')
  $$
  ;
  
SELECT concat_ws_athena('a', 'b')

CodePudding user response：

If you want to simulate exactly the output of this statement:

select array_to_string(array_construct_compact('a', 'b', 'c'), ',');

as seen here:

then your function should look like this:

CREATE OR REPLACE FUNCTION concat_ws_athena(s array)
  returns string
  as 
  $$
  array_to_string(s, ',')
  $$
  ;

and you would call it like this:

SELECT concat_ws_athena(['a', 'b', 'c']);

not passing 2 separate args but one arary with all args.

CodePudding user response：

Right now you cannot define a UDF with a variable number of input parameters. You can; however, overload UDFs so you could create a UDF with a variable set of input parameters that way. There would have to be some reasonable limit where you cut off the overloads. For example here the overloads allow 2, 3, or 4 parameters. The number could go much higher.

CREATE or replace FUNCTION concat_ws_athena(s1 string, s2 string)
  returns string
  called on null input
  as 
  $$
  array_to_string(array_construct_compact(s1, s2), '')
  $$
  ;
  
CREATE or replace FUNCTION concat_ws_athena(s1 string, s2 string, s3 string)
  returns string
  called on null input
  as 
  $$
  array_to_string(array_construct_compact(s1, s2, s3), '')
  $$
  ;
  
CREATE or replace FUNCTION concat_ws_athena(s1 string, s2 string, s3 string, s4 string)
  returns string
  called on null input
  as 
  $$
  array_to_string(array_construct_compact(s1, s2, s3, s4), '')
  $$
  ;
  
select concat_ws_athena('one','two',null,'three');

Also, most but not all Snowflake functions including UDFs will immediately return null if any input parameter is null. To override that behavior on UDFs, you can specify called on null input in the definition.