CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
PalimPalim
PalimPalim

Reputation: 3068

Snowflake UDF with variable number of inputs

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

Upvotes: 0

Views: 1155

Answers (2)

Greg Pavlik
Greg Pavlik

Reputation: 11086

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.

Upvotes: 1

Sergiu
Sergiu

Reputation: 4608

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

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

as seen here:

enter image description 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.

Upvotes: 1

Related Questions