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

David G. Johnston <david.g.johnston@gmail.com> wrote:
> On Mon, May 18, 2015 at 3:31 PM, Bruno Harbulot <bruno@distributedmatter.net>wrote:

>> In the discussion on the OpenJDK JDBC list two years ago
>> ( http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000050.html ),
>> Lance Andersen said "There is nothing in the SQL standard that
>> would support the use of an '?' as anything but a parameter
>> marker.".

> ​"​CREATE OPERATOR is a PostgreSQL extension. There are no
> provisions for user-defined operators in the SQL standard."

Exactly.  The standard specifies the characters to use for the
predicates that it defines, and provides no mechanism for adding
additional predicates; but who in the world would want to exclude
all extensions to the standard?

> ​And by extension if indeed the standard does require the use of
> "?" for parameters we are in violation there because the backend
> protocol deals with $# placeholders and not "?"​

We're talking about a different specification that has question
marks as parameter placeholders.  That's in the Java Database
Connector (JDBC) specification.  (It is apparently also specified
in other documents, although I'm not familiar enough with those to
comment.)  Note that it would create all sorts of pain if both the
SQL statements and a connector issuing them used the same
convention for substituting parameters; it is a *good* thing that
plpgsql and SQL function definitions use a different convention
than JDBC!

The JDBC spec provides for escapes using curly braces (including
product-specific escapes); it seems like a big mistake for us to
have chosen a completely different mechanism for escaping the
question mark character in a SQL statement.  Perhaps the least
painful path would be to add support for {?} as the escape for a
question mark, and a connection option to supplement that with
support for the legacy \? escape.  I would bet a lot of money that
even with an "if" test for that option, the curly brace escape
would be faster than what's there now (when the option was not
set).  Some operators would look a little funny in Java string
literals, but that's not so bad.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company