AWS Redshift Serverless how to pass multiple IDs to a stored procedure for IN clause (no temp tables and no dynamic SQL) please

Question

Hi! I am trying to create a store procedure to test passing multiple IDs to a stored proc to be used in the IN clause. In MySQL we pass json all the time. But I am unable to come up with a proper way to do so in Redshift. I saw some samples on creating some temp tables and perhaps using the dynamic SQL but I was told I could not use temp tables and/or dynamic sql for this project.

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

DECLARE
    BEGIN
     OPEN result FOR
  
    -- Need to extract all ID values to be used in the IN clause - not just first one. 
	-- 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;
$$
```

Here's how I am calling it:

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

**Many thanks in advance,**

Accepted Answer

Hello,

Greetings of the day!.

Thank you for reaching out with your concern in AWS re:Post.

Please find the below considerations for your use case.

============
1) For this I request you to please create a temporary table to store the values you pass in. You may not use json but instead, just request to pass in a string that represents an array of ids you are looking for.

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

And then unnesting it:

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

If you do it without a temporary table, you may see this error message:
select id from (select split_to_array('1,2,3', ',') as id_array) as sub, sub.id _array id;
ERROR:  Unnest subquery's result on leader is not supported

Or something like this

>>**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;
$$**

2) Another workaround, and probably better than a temp table, is to please make use of a dummy table like Oracle has built-in.

>>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, sub.id _array id;
 
Output:
id  
-----
 "1"
 "2"
 "3"
(3 rows)

You would replace '1,2,3' with the id list parameter you pass in. This would help to persist with the dual table.

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

In order to process json it has to be stored in a table/temp table before processing it.

I hope that answers your query.

Thank you and have a great day ahead!

Answer

I was hoping to convert json to SUPER using json_parse  and then use PartiQL but not matter what I've done I was unable to unflatten the json array without temp tables. So I ended up using a temp table and here's the solution I came up with:

```
CREATE OR REPLACE PROCEDURE pkg_util_test_passing_json(v_json_ids character varying(256), INOUT "result" refcursor)
 LANGUAGE plpgsql
AS $$

DECLARE

valid_json_array BOOLEAN;
     array_length INTEGER;
     i INTEGER := 0;
    
    BEGIN

CREATE TEMPORARY TABLE t (id INT);
    SELECT is_valid_json_array(v_json_ids) INTO valid_json_array;

IF ( valid_json_array ) THEN
		SELECT json_array_length(v_json_ids) INTO array_length; 
	END IF;

IF ( array_length > 0 ) THEN
		 
		<>
		LOOP
			-- RAISE INFO 'i %', i;
			INSERT INTO t(id) SELECT json_extract_path_text( json_extract_array_element_text(v_json_ids, i, true ) , 'id')::int;
			i := i + 1;
			EXIT simple_loop_when WHEN (i >= array_length);
		END LOOP;

OPEN result FOR 
			SELECT * FROM "my_table" WHERE my_id IN (SELECT id FROM t);

END IF;
END;
$$
```

AWS Redshift Serverless how to pass multiple IDs to a stored procedure for IN clause (no temp tables and no dynamic SQL) please

Output: id

Relevanter Inhalt