Thread

1. Parameter placeholders, $n vs ?

   Lele Gaifax <lele@metapensiero.it> — 2018-05-24T07:28:15Z

   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.
   
   
   
   

2. Re: Parameter placeholders, $n vs ?

   David G. Johnston <david.g.johnston@gmail.com> — 2018-05-24T12:17:52Z

   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.
   
   David J.
   

3. Re: Parameter placeholders, $n vs ?

   Tom Lane <tgl@sss.pgh.pa.us> — 2018-05-24T14:02:57Z

   "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
   
   
   

4. Re: Parameter placeholders, $n vs ?

   Lele Gaifax <lele@metapensiero.it> — 2018-05-24T18:58:16Z

   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.