(problem has been resolved, see answer below)
I'm trying to run a query that uses the INTERVAL syntax with the value from a select component used as a variable in the query, but get different errors depending on my implementation.
The error seems to stem from the fact that INTERVAL format is picky about how you give it the format and the templating language is quoting my string values slightly off.
I initially used this query:
SELECT
DATE("publishedAt") AS date,
COUNT(*)::INTEGER AS count
FROM
"Video"
WHERE
DATE("publishedAt") > NOW() - INTERVAL {{new_videos_days.value}}
GROUP BY
DATE("publishedAt")
ORDER BY
date ASC;
My select dropdown assigns a value of 7 days (a string):
This results in the query using "7 days" (note the double quotes):
...and the following error:
"syntax error at or near "$1""
This is a problem since this generated SQL that contains DATE("publishedAt") > NOW() - INTERVAL "31 day" (with double quotes), which is not valid SQL. I need it to be DATE("publishedAt") > NOW() - INTERVAL '31 day' (single quotes).
If I try an alternate way to inject the value...
DATE("publishedAt") > NOW() - INTERVAL '{{new_videos_days.value}} days'
...and use a drop-down value of just 7, which makes it list it as an number...
...but I get a new error...
"bind message supplies 1 parameters, but prepared statement "" requires 0"
...despite it using just 7 without any quotes (presumably used as an integer):
Am I quoting the variable wrong? Is there some kind of explicit casting I could do to solve this issue?
Is there a way to get the full SQL that a query tried to use BTW, rather than the 'SQL Prepared Statement' format shown above?
I'm using PostgreSQL, but not sure if this is specific to that DB type. A similar problem seems to have been noted before, but the proposed solution doesn't seem to address the issue of the SQL that needs to consume the select component's value.