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.

Answer 1

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 => []));