在AWS Redshift Serverless中，如果不使用临时表和动态SQL，如何将多个ID传递给存储过程的IN子句？

Question

【以下的问题经过翻译处理】 您好！我正在尝试创建一个存储过程，用于测试将多个ID传递给存储过程，并在IN子句中使用。在MySQL中，我们经常使用JSON来传递参数。但是我无法找到在Redshift中正确实现此功能的方法。我看到了一些关于创建临时表和使用动态SQL的示例，但是我被告知在这个项目中不能使用临时表和/或动态SQL。

以下是您提供的存储过程的代码：

CREATE OR REPLACE PROCEDURE "pkg_util_test_passing_json"( v_json_ids varchar, INOUT "result" refcursor)
 LANGUAGE plpgsql
AS $$

DECLARE
    BEGIN
     OPEN result FOR
  
    -- 需要提取所有ID值以在IN子句中使用，而不仅仅是第一个ID。
	-- SELECT json_extract_path_text( json_extract_array_element_text(v_json_ids, 0, true ) , 'id');

SELECT * FROM mytable WHERE id IN (SELECT * FROM ?????);

END;
$$

这是我调用存储过程的方式：

BEGIN;
CALL "pkg_util_test_passing_json"('[{"id":31997051},{"id": 31997052}, {"id": 31997053}]', 'result');  
FETCH ALL FROM result;

非常感谢！

Answer

【以下的回答经过翻译处理】 你好，

祝你有美好的一天！

感谢您在AWS re:Post中提出您的问题。

请查看下面针对您的问题的思考。

============

为此，请您创建一个临时表来存储您传递的值。您可以不使用JSON，而是请求传递一个表示您要查找的ID数组的字符串。

例如：

create temporary table t as select split_to_array('1,2,3', ',') as id_array;

然后行去关联：

select id from (select id_array from t) as sub, unnest(sub.id_array) as id;

如果不使用临时表，您可能会看到以下错误消息：select id from (select split_to_array('1,2,3', ',') as id_array) as sub, unnest(sub.id_array) as id; ERROR: Unnest subquery's result on leader is not supported

或者类似下面的错误：

CREATE OR REPLACE PROCEDURE "pkg_util_test_passing_json"(v_json_ids varchar, INOUT "result" refcursor) LANGUAGE plpgsql AS $$ DECLARE BEGIN drop table if exists tempIds; create temp table tempIds as SELECT JSON_PARSE(v_json_ids) as ArrId; OPEN result FOR SELECT * FROM mytable WHERE id IN (select e.id from tempIds as t, t.ArrId as e at index); END; $$

另一个解决方法，可能比临时表更好，是使用像Oracle内置的伪表一样的虚拟表。

create table dual (dummy varchar(1)); insert into dual values ('x');

select id from (select split_to_array('1,2,3', ',') as id_array from dual) as sub, unnest(sub.id_array) as id;

输出：id

"1" "2" "3"（共3行）

您可以将'1,2,3'替换为传递的ID列表参数。这将有助于使用虚拟表持久化。

=============

为了处理JSON，必须将其存储在表/临时表中，然后再进行处理。

希望回答了您的问题。

谢谢，祝您有美好的一天！

在AWS Redshift Serverless中，如果不使用临时表和动态SQL，如何将多个ID传递给存储过程的IN子句？

相关内容