英文:
Snowflake UDF with Array Input Parameter
问题
我正在Snowflake中创建一个新的SQL UDF,UDF已成功创建。但是当我在调用UDF时传递值时,出现以下错误。
> 001044 (42P13): SQL编译错误:错误行1,位置20。
> 函数'FNTUSERLIST'的参数类型无效:(ARRAY)
以下是重现相同问题的步骤。
创建表
CREATE TABLE UserLoad(
Idx Int identity(1,1),
UserName varchar(1000),
UserAge int
)
插入数据
insert into UserLoad(UserName,UserAge)
select 'Rahul',39 UNION
select 'Sankalp',38 UNION
select 'Arun',35 ;
创建函数
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
$$;
调用函数
select * from table(fntuserList(ARRAY_CONSTRUCT('1','2')));
请注意,我希望在逻辑内部进一步使用输入参数。但是目前无法调用函数,因此底层逻辑不完整。
英文:
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.
答案1
得分: 1
Function fntuserList
期望提供两个数组:
- 空数组
select * from table(fntuserList(['1','2'], []));
- NULL 值
select * from table(fntuserList(['1','2'], NULL::ARRAY));
为了提高可读性,可以使用命名参数:
select *
from table(fntuserList(I_UserName => ['1','2'], I_UserAge => []));
英文:
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 => []));
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论