Thread: Parameter placeholders, $n vs ?

Hi all,

while improving the technical documentation of my current project, I hit a SQL
statement that triggered a bug in my pg_query[1] based prettifier.

The statement in question was using '?' as param placeholders instead of the
'$n' style I'm used to: to my surprise the parser (pg_query uses the nice
standalone packaging of the PG parser provided by libpg_query[2]) accepted it
without problems, although emitting an AST that the prettifier was not able to
cope with.

Briefly:

  $ diff -u <(echo 'SELECT * FROM foo WHERE bar = $1' | pgpp -t) \
            <(echo 'SELECT * FROM foo WHERE bar = ?' | pgpp -t)
  --- /dev/fd/63    2018-05-24 09:13:40.877301119 +0200
  +++ /dev/fd/62    2018-05-24 09:13:40.877301119 +0200
  @@ -56,8 +56,7 @@
                 ],
                 "rexpr": {
                   "ParamRef": {
  -                  "location": 30,
  -                  "number": 1
  +                  "location": 30
                   }
                 }
               }

While the fix to pg_query was trivial, to satisfy my own curiosity I looked
around to get evidence of whether the '?' style is officially accepted or
what.

The ParamRef documentation[3] does not mention that the "number" may be
"missing", and given that '?' is very difficult to search I failed to find a
definitive answer.

So the questions: is the '?' style placeholder a supported variant? and if so,
should the ParamRef doc tell something about that?

Thanks in advance,
ciao, lele.

[1] https://github.com/lelit/pg_query/
[2] https://github.com/lfittl/libpg_query
[3]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/nodes/parsenodes.h;h=6390f7e8c129e84607e1bb3c56ddd8578115f298;hb=HEAD#l243
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
lele@metapensiero.it  |                 -- Fortunato Depero, 1929.

So the questions: is the '?' style placeholder a supported variant? and if so,
should the ParamRef doc tell something about that?

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, May 24, 2018, Lele Gaifax <lele@metapensiero.it> wrote:
>> So the questions: is the '?' style placeholder a supported variant? and
>> if so, should the ParamRef doc tell something about that?

> PostgreSQL's Prepare statement doesn't accept question mark as a parameter
> symbol, and cannot ever because it is already a valid operator symbol.

To enlarge on that a bit:

* PG's core parser certainly does not accept ? as a parameter symbol.
I speculate that you fed the input through some frontend that converts
? to $n (JDBC, perhaps)?

* The only thing in the core code that would print a ParamRef in any
sort of symbolic form is _outParamRef, and it's easily seen by inspection
to be incapable of omitting the "number" field ... not to mention that
it doesn't emit the JSON-esque representation you're showing us.  So
that behavior must also be due to some non-core pretty-printing code you
haven't identified to us.  No idea what rules that might have for deciding
to omit "number".

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> On Thursday, May 24, 2018, Lele Gaifax <lele@metapensiero.it> wrote:
>>> So the questions: is the '?' style placeholder a supported variant? and
>>> if so, should the ParamRef doc tell something about that?
>
>> PostgreSQL's Prepare statement doesn't accept question mark as a parameter
>> symbol, and cannot ever because it is already a valid operator symbol.
>
> To enlarge on that a bit:
>
> * PG's core parser certainly does not accept ? as a parameter symbol.
> I speculate that you fed the input through some frontend that converts
> ? to $n (JDBC, perhaps)?
>
> * The only thing in the core code that would print a ParamRef in any
> sort of symbolic form is _outParamRef, and it's easily seen by inspection
> to be incapable of omitting the "number" field ... not to mention that
> it doesn't emit the JSON-esque representation you're showing us.  So
> that behavior must also be due to some non-core pretty-printing code you
> haven't identified to us.  No idea what rules that might have for deciding
> to omit "number".

Thank you to both. As said, the tool is built on the PG 10 parser extracted as a
standalone library, that serializes the statement AST as JSON. I assumed it
didn't diverge much from what the core parser accepts.

I will clarify my doubt with libpg_query's author.

bye, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
lele@metapensiero.it  |                 -- Fortunato Depero, 1929.