Postgres and other database interfaces

[kdevel]

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