问题

以下是翻译好的部分：

"Actually I am passing I_UserName as array parameter in UDF and then FLATTEN to table for comparing the rows. I am trying to avoid in clause based queries therefore taking array as input parameter."

"实际上，我将 I_UserName 作为数组参数传递给 UDF，然后将其展开为表格以进行行比较。我试图避免使用 IN 子句的查询，因此将数组作为输入参数。"

"Invalid argument types for function 'PARSE_JSON': (ARRAY)"

"函数 'PARSE_JSON' 的参数类型无效：(ARRAY)"

"CREATE OR REPLACE FUNCTION fntuserList_New (I_UserName array, I_UserAge array)
RETURNS TABLE (Id int, UserName varchar(256), UserAge int)
AS
$$
SELECT
Idx AS Id,
ul.UserName,
ul.UserAge
FROM
UserLoad ul
INNER JOIN LATERAL FLATTEN(input => PARSE_JSON(I_UserName)) AS usr
ON UPPER(usr.VALUE::string) = UPPER(ul.UserName)
$$;"

"CREATE OR REPLACE FUNCTION fntuserList_New (I_UserName array, I_UserAge array)
RETURNS TABLE (Id int, UserName varchar(256), UserAge int)
AS
$$
SELECT
Idx AS Id,
ul.UserName,
ul.UserAge
FROM
UserLoad ul
INNER JOIN LATERAL FLATTEN(input => PARSE_JSON(I_UserName)) AS usr
ON UPPER(usr.VALUE::string) = UPPER(ul.UserName)
$$;"

CREATE OR REPLACE FUNCTION fntuserList_New (I_UserName array, I_UserAge array)
RETURNS TABLE (Id int, UserName varchar(256), UserAge int)
AS 
$$
            SELECT
                Idx AS Id,
                ul.UserName,
                ul.UserAge 
            FROM  
                UserLoad ul
            INNER JOIN LATERAL FLATTEN(input => PARSE_JSON(I_UserName)) AS usr 
            ON UPPER(usr.VALUE::string) = UPPER(ul.UserName)
        $$;

答案1

得分: 1

PARSE_JSON(I_UserName) 无法接受数组作为输入。

I_UserName 在函数头部分被定义为数组：

CREATE OR REPLACE FUNCTION fntuserList_New (I_UserName array, I_UserAge array)

要么更改函数的定义，要么将该数组转换为PARSE_JSON()可以解析的内容。

CREATE OR REPLACE FUNCTION fntuserList_New (I_UserName array, I_UserAge array)

答案2

得分: 1

只需删除PARSE_JSON函数：

CREATE OR REPLACE FUNCTION fntuserList_New (I_UserName array, I_UserAge array)
RETURNS TABLE (Id int, UserName varchar(256), UserAge int)
AS 
$$
    SELECT
        Idx AS Id,
        ul.UserName,
        ul.UserAge 
    FROM  
        UserLoad ul
    INNER JOIN LATERAL FLATTEN(input => I_UserName ) AS usr 
    ON UPPER(usr.VALUE::string) = UPPER(ul.UserName)
$$;

CREATE OR REPLACE FUNCTION fntuserList_New (I_UserName array, I_UserAge array)
RETURNS TABLE (Id int, UserName varchar(256), UserAge int)
AS 
$$
    SELECT
        Idx AS Id,
        ul.UserName,
        ul.UserAge 
    FROM  
        UserLoad ul
    INNER JOIN LATERAL FLATTEN(input => I_UserName ) AS usr 
    ON UPPER(usr.VALUE::string) = UPPER(ul.UserName)
$$;