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]https://github.com/lelit/pg_query/ 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]https://github.com/lfittl/libpg_query) 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]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. 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.
--
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.
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.
"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.