Skip to content
Advertisement

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:

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:

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement