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
Advertisement
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;