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

0

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,

MZ
asked a year ago367 views
2 Answers
0
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; $$

  1. 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!

AWS
answered a year ago
0

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
		 
		<<simple_loop_when>>
		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;
$$
MZ
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions