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

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

Answer

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;