Include single quote string in redshift dynamic SQL

Question

I have function named

Schema.func_date(date,varchar)

I wanna do like this(I am using below statement inside Store proc)

Execute 'Select *, ID as Main_Id, ' ||
 'schema.func_date(quote_literal('2020-02-20'),quote_literal('ST')), '||
 'from main_table'

I am getting Invalid operation error while passing single quote string. How to pass the single quote string perfectly in execute statement?

Answer 1

While the QUOTE_LITERAL() function is helpful in specific contexts, I think you still need to manually escape the single quotes when you use Dynamic SQL.

So your query should follow this:

Execute 'Select *, ID as Main_Id, ' ||
 'schema.func_date(quote_literal(''2020-02-20''),quote_literal(''ST'')), '||
 'from main_table'