Re: jsonb problematic operators

On 11 December 2016 at 18:52, Geoff Winkless <gwinkless(at)gmail(dot)com> wrote:
> On 9 Dec 2016 17:54, "Andres Freund" <andres(at)anarazel(dot)de> wrote:
>
> On 2016-12-09 12:17:32 -0500, Robert Haas wrote:
>> As Geoff says, you don't have to use the operators; you could use the
>> equivalent functions instead. Every operator just gets turned into a
>> function call internally, so this is always possible.
>
> Well, except that only operators support indexing :(
>
>
> Really? Seems like an odd design decision.
>
> The only other simple suggestion then would be to use PDO named parameters
> instead of positional ones. Much nicer syntax anyway, IMO.

You can also create alternate names for the operators, but it's a bit
of a pain if you want indexing support. Though I thought we defined
alternative names for exactly this reason, but I don't see them...

It's definitely annoying, in both directions. ? wasn't a great choice
for an operator character but it's logical and was grandfathered over
from hstore. PDO not offering any way to escape parameter binding
characters is at least as bad. What client interface provides no way
to pass-through strings it would otherwise treat as special?

Does PDO cope if you use the OPERATOR("pg_catalog".?) form? Or does it
still try to bind the parameter? e.g.

postgres=> SELECT '{}'::jsonb OPERATOR("pg_catalog".?) 'fred';
?column?
----------
f
(1 row)

Does PDO let you double question marks to escape them, writing ?? or
\? instead of ? or anything like that?

If not, I suggest that you (a) submit a postgres patch adding
alternative operator names for ? and ?|, and (b) submit a PDO patch to
allow ?? or \? as an escape for ? .

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services