On Sunday, 25 February 2018 at 11:46:26 UTC, Denis F wrote:
But it is impossible to convert text :names or '?' into
Postgres's "$1": Postgres isn't knows fields names at start of
a query processing and you can't replace '?' to "$<num>" by
simple 'replace' call - it will need full syntax parsing of
Posgres SQL query because queries can contain EXECUTE statement
(whose purpose is executing dynamic commands).
You don't have to parse the full syntax. Quote from another
language (which also supports underscore in integer literals):
'Placeholders
There are three types of placeholders that can be used in
DBD::Pg. The first is the "question mark" type, in which each
placeholder is represented by a single question mark character.
This is the method recommended by the DBI specs and is the most
portable. Each question mark is internally replaced by a "dollar
sign number" in the order in which they appear in the query
(important when using "bind_param").
The second type of placeholder is "dollar sign numbers". This is
the method that Postgres uses internally and is overall probably
the best method to use if you do not need compatibility with
other database systems. DBD::Pg, like PostgreSQL, allows the same
number to be used more than once in the query. Numbers must start
with "1" and increment by one value (but can appear in any order
within the query). If the same number appears more than once in a
query, it is treated as a single parameter and all instances are
replaced at once. [...]
The final placeholder type is "named parameters" in the format
":foo". While this syntax is supported by DBD::Pg, its use is
discouraged in favor of dollar-sign numbers.
The different types of placeholders cannot be mixed within a
statement, but you may use different ones for each statement
handle you have. This is confusing at best, so stick to one style
within your program.
If your queries use operators that contain question marks (e.g.
some of the native Postgres geometric operators and JSON
operators) or array slices (e.g. data[100:300]), there are
methods to instruct DBD::Pg to not treat some symbols as
placeholders. First, you may simply add a backslash before the
start of a placeholder, and DBD::Pg will strip the backslash and
not treat the character as a placeholder.
You can also tell DBD::Pg to ignore any non-dollar sign
placeholders by setting the pg_placeholder_dollaronly attribute
at either the database handle or the statement handle level.
[...] ' [1]
[1]
http://search.cpan.org/~turnstep/DBD-Pg-3.7.4/Pg.pm#Placeholders