by Bhuvanesh

Tags

RedShift stored procedures are really useful feature and after a long time they introduced it. I tried this stored procedure for many use cases and recently I came up with a complexity in it. You can’t get a comma separated sting in the argument. Lets say I have an employee table and I want to pass the employee names in a variable/argument and you need to select * from table where employee_name in ('your comma separated values). It’ll not work directly.

Why? many SQL peoples already know the reason. Because you have multiple values in one string and you need to parse it as multiple quotes string and pass it.

Example:

Input: 'emp1, emp2, emp3'

In SQL you can’t use IN clause like this. You need to split each and every value with single quote. Then it should be

'emp1', 'emp2', 'emp3'

In plpgsql, you can do this parsing with some temp table snd SQL queries. I have asked the steps in DBA’s stackoverflow and a horse with no name (yeah, its a weird name) answered it.

But in Redshift it did’t work as expected.

What’s wrong in Redshift:

Before checking why its not working, we’ll see how are we thinking to parsing it.

1. Pass the argument into a variable.
2. Use trim functions to remove the single quote.
3. Use the split function to split these values.
4. For each values put single quote in the beginning and the end.

Or as mentioned the stackoverflow answer, we can’t use arrays for this. Thats why its not working and it’ll throw the following error.

Then I searched many blogs and found the useful information.

I almost fixed the issue. But now the other problem came when I do query the Information_schema tables. My select query will look like below.

While running this, I got the below error.

Its saying we don’t have enough permissions. So I need to reached out AWS support team to help on this one piece. Then they got help from RedShift product team to write the correct SQL query.

Stored Procedure with multiple values:

With my stored procedure, I want to get the list of table names from the argument and print its name along with schema.

Another Scanrio:

I have a test table called bhuvi and I have 3 rows in it.

Now I want to run a select query where name in b and c. So in the above stored procure, I just edited the below FOR LOOP part.

Call the Procedure

More Customization:

• If you would to have a permanent table, then create it in your database and remove the With NS as
• This will only extract upto 256 I guess, if you inputing more than 10 then refer this.