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

[Dmitry Igrishin]

2017-08-27 18:32 GMT+03:00 Dmitry Igrishin :

>
>
> 2017-08-27 18:13 GMT+03:00 Tom Lane :
>
>> Dmitry Igrishin  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.

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

[Dmitry Igrishin]

2017-08-27 18:13 GMT+03:00 Tom Lane :

> Dmitry Igrishin  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.)

Thanks!

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

[Tom Lane]

Dmitry Igrishin  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?
For instance, this is legal:

regression=# select $foobar$stuff$foobar$;
 ?column? 
--
 stuff
(1 row)

I think you're going to end up with weird corner case behaviors if
you try to squeeze still another meaning into "$letters..."

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Using the dollar sign as a prefix for named parameter of prepared statement.

[Dmitry Igrishin]

Hi all,

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?

According to
https://www.postgresql.org/docs/10/static/sql-syntax-lexical.html

a) "SQL identifiers and key words must begin with a letter ..."
b) "The tag, if any, of a dollar-quoted string follows the same rules as an
unquoted identifier ... "
c) "A dollar sign ($) followed by digits is used to represent a positional
parameter in the body of a function definition or a prepared statement. In
other contexts the dollar sign can be part of an identifier or a
dollar-quoted string constant."

I don't see any problems with such a choise. Am I missing something?

Thanks!