Problem: When using a double colon (which is often used in postgesql) query in a queryExecute, Lucee expects a query parameter. I see no way to have double colons AND parameter in a query.

Proposed solution: The possibility to escape characters with a \ characters like other JDBC driver do.

Example (Without parameter, but clear enough to describe the issue):

qGetJourneyDisplay = queryExecute("
SELECT journey_title, journey_id FROM baba.journey JOIN unnest(’{5,3,1,2,4}’::int) WITH ORDINALITY AS x(journey_id, order_nr) USING (journey_id) ORDER BY x.order_nr
",{},{returntype=“query”});

Lucce has a problem with a double colon in the quety and eats one : away, so the query is send to the db as follow:

SELECT journey_title, journey_id FROM babawanga.journey JOIN unnest(’{5,3,1,2,4}’:int) WITH ORDINALITY AS x(journey_id, order_nr) USING (journey_id) ORDER BY x.order_nr

Result:
ERROR: syntax error at or near “:”

So the query fails. Possible solution:

qGetJourneyDisplay = queryExecute("
SELECT journey_title, journey_id FROM baba.journey JOIN unnest(’{5,3,1,2,4}’::int) WITH ORDINALITY AS x(journey_id, order_nr) USING (journey_id) ORDER BY x.order_nr
",{},{returntype=“query”});