php - How to prevent PDO from interpreting a question mark as a placeholder?

Question

For detecting the existence of a key in a hstore, I need to run a query like this:

SELECT * FROM tbl WHERE hst ? 'foo'

However, that gives me a PDOException:

PDOException: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound: SELECT * FROM tbl WHERE hst ? 'foo'

Is there any way to escape the question mark so PDO won't pick it up as a placeholder? I've tried with up to four backslashes, as well as a double question mark (??), but nothing seems to persuade PDO to leave the question mark alone.

Answer 1

Use the function call form. According to the system catalogs, the hstore ? operator uses the exist function:

regress=# select oprname, oprcode from pg_operator where oprname = '?';
 oprname | oprcode 
---------+---------
 ?       | exist
(1 row)

so you can write:

SELECT * FROM tbl WHERE exist(hst,'foo');

(Personally I'm not a big fan of hstore's operator-centric design and documentation, I think it discards the useful self-documenting properties of a function based interface without any real benefit and I usually use its function calls rather than its operators. Just because you can define operators doesn't mean you should.)