Snowflake UDF with Array Input Parameter

Question

I am creating a new SQL UDF in Snowflake and UDF is created successfully. But when I am passing value while calling the UDF, I am getting below error.

001044 (42P13): SQL compilation error: error line 1 at position 20.

Invalid argument types for function 'FNTUSERLIST': (ARRAY)

Here are the steps to replicate the same.

Create Table

CREATE TABLE UserLoad(
Idx Int identity(1,1),
UserName varchar(1000),
UserAge int

)

Insert Data

insert into UserLoad(UserName,UserAge)
select 'Rahul',39 UNION 
select 'Sankalp',38 UNION 
select 'Arun',35 ;

Create Function

CREATE OR REPLACE FUNCTION fntuserList (I_UserName array,I_UserAge array)
RETURNS TABLE (Id int,UserName varchar(256),UserAge int)
AS 
$$
    select Idx as Id,UserName,UserAge from  UserLoad
$$;

Call the function

select * from table(fntuserList(ARRAY_CONSTRUCT('1','2')));

Please note that I want to use the input parameters further in sql inside the logic. But as of now unable to call function therefore underlaying logic is incomplete.

Your function takes input as 2 arrays, but you're only passing one in your select (ARRAY_CONSTRUCT returns one array with 2 values which is different from 2 individual arrays). — Sergiu, Jun 15 '23 at 12:38
Oversight in hurry. Thanks for the quick comment. Do you have any idea how input parameters can be optional — Rahul Neekhra, Jun 15 '23 at 12:40
I don't believe SF supports optional input parameters. In the past I've got round this by passing in a "default" value in any "optional" parameter and handling these default values in the code — NickW, Jun 15 '23 at 12:58

score 1 · Accepted Answer · answered Jun 15 '23 at 16:39

Function fntuserList expects two arrays and they should be provided:

-- empty array
select * from table(fntuserList(['1','2'], []));

-- NULL value
select * from table(fntuserList(['1','2'], NULL::ARRAY));

Also for readability named arguments could be used:

select * 
from table(fntuserList(I_UserName => ['1','2'], I_UserAge => []));

Snowflake UDF with Array Input Parameter

1 Answers1