-
Goal:
I am trying to add a slightly complex WHERE clause to my query, I keep getting syntax errors, but the error messages do nothing to help me understand what's wrong with the syntax. Specifically, the error messages tell me there's a syntax error in the prepared statement near$3, but the actual prepared statement including that$3is omitted -
Steps:
I've tried several permutations on the formatting of my code, but without seeing the prepared statement and the parameters passed into it, I'm flying blind. -
Details:
I'm trying to add a condition to the effect ofwhere intake_date >= (CURRENT_TIMESTAMP - interval '30 days')with the30replaced by a variable which defaults to 365 if not specified, the syntax psql uses for interval expressions does not play nicely with the way retool variables work, and I've tried several permutations with no success, here's just a couple of examples to get the idea across, though I've tried dozens:
and intake_date >= (CURRENT_TIMESTAMP - interval {{ '\'' + (startTrigger.data.backdate_days || 365) + ' days\'' }})
results in an " syntax error at or near "$3" error
and intake_date >= (CURRENT_TIMESTAMP - {{ 'interval \'' + (startTrigger.data.backdate_days || 365) + ' days\'' }})
results in an "operator does not exist: timestamp without time zone >= integer" error
While I'd welcome any suggestions on how to structure this expression and fix the error, I think the deeper issue is the fact that I cannot actually see the exact prepared statement that was sent to the db.
Here are screenshots of the errors I get back:
The workflow editor does try to preview prepared statements while editing:
but this is NOT the actual statement that gets sent to the database, it's just the editors best approximation of the transformation my code will go through before being sent to the DB, and it's not accurate, so it's not useful.
This isn't the first time I've been frustrated by these unhelpful error messages, basically any sql syntax error is made substantially harder to diagnose because of this limitation, but this one has proven to be far more difficult to resolve so I finally decided it was worth the time to write up my complaints.
P.S. I've tried searching the forums for people facing issues with similar queries, and almost every thread about the topic ends with the OP being instructed to disable prepared statements for their entire application, which is a terrible solution. The risks of doing that are generally pointed out, but it's still a wildly heavy-handed solution that almost nobody should use. It'd be great if retool could add the option to disable prepared statements for individual query blocks, rather than it needing to be done at the resource level.
EDIT: for anyone trying to implement a similar query to the one I reference in this post, I eventually settled on moving the date arithmetic out of the SQL query. Now I compute the equivalent of (CURRENT_TIMESTAMP - interval '{{(startTrigger.data.backdate_days || 365)}} days') in javascript, then use that value in my query like so: where intake_date >= {{compute_timestamp.data}} where compute_timestamp is a block that returns a js Date representing the appropriate timestamp.
