雪花UDF与数组输入参数

huangapple go评论64阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年6月15日 20:25:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/76482449.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定