How can I pass a list, array or string to be separated as a parameter to redshift

user_11746094 0
Viewed 1038

I'm trying to write a simple query with an in clause like so:

SELECT * 
FROM storeupcsalesbyday
WHERE date >= '9/1/2020' AND date <= '9/10/2020' AND upc in ('0000000004011', '0000000094011')

I need to be able to pass the values in the in clause as a parameter, the number of values in the in clause are variable and could be one or thousands depending on the user input. In other sql databases I have solved this problem by creating a user defined function that takes a string, splits it on a delimiter and inserts the values in a temp table, then I would select all from the temp table to use in my in clause. However user defined functions in redshift do not allow tables as a return type. How are others solving this problem in redshift.

Thanks

amazon-redshift amazon-web-services sql
2 Answers

I was able to create a stored procedure that takes a varchar and creates a temp table of all "slices" of the varchar broken up by a delimiter (in this case a ','). I just wanted to share it here in case someone else has this issue.

Here is the procedure:

CREATE OR REPLACE Procedure sp_UPCStringToTempTable(upcList IN varchar(max))
AS 'DECLARE
  idx int;
  slice varchar(8000);
  upcListVar varchar(max);
BEGIN
  idx = 1;
  upcListVar = upcList;
  DROP TABLE if exists tmp_upc;
  CREATE TEMP TABLE tmp_upc(upc varchar(14));
  WHILE idx != 0 LOOP
    idx = charindex('','', upcListVar);
    IF idx != 0 THEN
      slice = left(upcListVar, idx - 1);
    END IF;
    IF idx = 0 THEN
      slice = upcListVar;
    END IF;
    IF len(slice) > 0 THEN
      INSERT INTO tmp_upc values (slice);
    END IF;
    upcListVar = right(upcListVar, len(upcListVar) - idx);
  END LOOP;
END;
' LANGUAGE plpgsql;
user_117460940 
 create table num(id int) ;
 insert into num values(1), (2),(3);
 with t as 
 (
 select split_part('0000000004011, 0000000094011',',',id ) col1 from num 
 ) 
 select * from a join  t on a.col1 = t.col1

This should solve your problem.

user_137220990
Related