Use Redshift query result as to_char format

Question

Inspired by the slected answer to Declare a variable in RedShift I am trying to use a query result as the format value in a to_char function call:

WITH tmp_variables as (
    select 'YYYY-MM-DD' as date_format
)
SELECT to_char(OrderDate, (SELECT date_format FROM tmp_variables)) FROM Orders

But I am getting an error

TO_CHAR parameter: Second input must be a string literal

How can the tmp_variables's date_format value be used as a to_char format without getting an error or is there an alternative to using to_char where this would work?

I am making the question a bit broader. Basically, is there any alternative to using `to_char` to make this work? — Daniel A. R. Werner, Jul 28 '21 at 16:48
Use `CASE`. (Extra padding for minimum message length. The devs have too much time on their hands.) — , Jul 28 '21 at 18:27

score 1 · Answer 1 · answered Jul 28 '21 at 18:25

SELECT is a SQL operator that work upon data. SQL is compiled before it can operate on data. The basic answer is that this won't work as written.

What you are trying to achieve isn't clear in the question - change date output format for some reason for some set of queries but not others? In the general case you will need to modify the SQL that goes to the compiler which will mean reading some configuration and merging this into the SQL text. If the use case is more limited there may be another way to achieve the desired result but only within some set of limitations.

Some possibilities - You could set a SQL variable with the format literal. Your client can read info and modify the query itself if it is capabile. A stored procedure could be used. A SQL modifier (pg_bouncer?) could live between the client and the cluster and substitute the string based on some other factors. Each of these has limitations and costs.

If you can describe the use case it could generate different / better ways.

Use Redshift query result as to_char format

1 Answers1