Re: Problems with question marks in operators (JDBC, ECPG, ...)

On Tue, May 19, 2015 at 5:34 PM, Bruno Harbulot
<bruno(at)distributedmatter(dot)net> wrote:
> Users of question mark operators are already admitting their application and
> code isn't portable (since they are specific to PostgreSQL and its
> extensions). The problem has more to do with how the other tools around
> handle these customisations. For example, it can be useful to have a model
> based on Hibernate in Java and be able to use ? operators for specific
> features. (Other tools like SQLAlchemy in Python also allow you to have
> customisations specific to the RDMBS platform, while being able to use the
> core features in a more platform-neutral way.)
>
> It turns out that you can indeed use ? in JSONB with a custom Hibernate
> query, you just need to double-escape it as follows: ? becomes ?? and has to
> be escaped as \?\?, but \ has to be escaped itself...
>
> SQLQuery query = session
> .createSQLQuery("SELECT
> CAST((CAST('{\"key1\":123,\"key2\":\"Hello\"}' AS jsonb) \\?\\? CAST(? AS
> text)) AS BOOLEAN)");
> query.setString(0, "key1");

I think we should be more focused on this part of the issue. It seems
to me that it's a good idea for connectors to have an escaping
mechanism. Pretty much any syntax that supports funny characters that
do magical things should also have a way to turn the magic off when
it's not wanted. But it's not a bad thing either for the core project
to try to steer around operator names that are likely to require
frequent use of that escaping mechanism.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company