Re: Using the dollar sign as a prefix for named parameter of prepared statement.

2017-08-27 18:32 GMT+03:00 Dmitry Igrishin <dmitigr(at)gmail(dot)com>:

>
>
> 2017-08-27 18:13 GMT+03:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
>> Dmitry Igrishin <dmitigr(at)gmail(dot)com> writes:
>> > I'm working on finishing beta release of my C++ API for PostgreSQL. The
>> > library
>> > have simple SQL parser (preprocessor) to support the queries like that:
>>
>> > SELECT :"column", $tag$constant string$tag$
>> > FROM :tables
>> > WHERE name LIKE :'name' AND
>> > sex = $1 AND
>> > age > $age
>>
>> > where:
>> > :"column" is a variable which will be quoted as identifier (like in
>> > psql),
>> > :tables is a variable which will be not be quoted at all (like in
>> psql),
>> > :'name' is a variable which will be quoted as literal (like in
>> psql),
>> > $1 is a positional parameter,
>> > $age is a named parameter
>>
>> > Is there are any contraindications/caveats/gotchas on using the dollar
>> sign
>> > as a prefix for the named parameters?
>>
>> How are you going to distinguish named parameters from dollar-quote tags?
>>
>
> Well, since "The tag, if any, of a dollar-quoted string follows the same
> rules as an unquoted identifier ... ",
> dollar-quote tags cannot contain spaces, right? This fact can be used to
> distingush
> named parameters from dollar-quote tags.
> If so, the only problem I see here is legalisation of spaces in the tags
> in future releases, for example:
> select $foo bar$stuff$foo bar$;
>
>>
>> I think you're going to end up with weird corner case behaviors if
>> you try to squeeze still another meaning into "$letters..."
>>
> But yes, probably it is better to use another syntax for named parameters.
> How about ":_parameter_"?
> (Looks not so nice as "$parameter", but compatible with psql and can be
> used as easy query testing.)
>
Another option is to use ":" as a prefix for named variables, which can be
replaced by the user with any text before preparing the statement. When the
statement is about to be prepared, variables which are replaced should be
considered
as parameters and replaced with $1, $2, ... by preprocessor.