Having trouble with this dynamic psql query in Retool. I've tried a lot of variations of this. Here is the query without templating, which runs as expected:
SELECT * FROM ability WHERE "action" -> 'linkUrl' @> '"https://poolsuite.net/"'::jsonb;
But as soon as I put any sort of handlebar template in place of that url, it fails with invalid input syntax for type json
Here is what is written in retool in the query:
SELECT * FROM ability WHERE "action" -> 'linkUrl' @> '{{AbilitiesTable.selectedRow.data.action.linkUrl}}'::jsonb;
As you can see in this screenshot, the brackets/handlebars should resolve to the same string as in the hard coded example above:
It seems that Retool is using a prepared statement to make the handlebars work, so I tested the query with a prepared statement in a database management tool (TablePlus), and it worked the same as the above query, as expected.
PREPARE ability_query (text) AS SELECT * FROM ability WHERE "action" -> 'linkUrl' @> $1::jsonb; EXECUTE ability_query ('"https://poolsuite.net/"');
What am I doing wrong?
