On Wed, Sep 18, 2019 at 12:34:54PM +0300, Lassi Kortela wrote:
> In expansion syntax, I've learned from experience to prefer some kind of
> brackets around the identifier being expanded, instead of only having a
> prefix (dollar sign, colon, etc.)

Yeah, that's probably a good idea.  It balances nicely and allows you to
more easily navigate over them using something like "next expression" in
a proper editor.

> When you use many languages you tend to
> forget what characters are valid in the identifier; the explicit closing
> bracket makes it obvious where the identifier ends. Templating languages
> also tend to use braces around identifiers, and they have to operate in some
> of the most hostile syntactic environments around :p
>
> Maybe even one pair of braces would be enough since the user can choose
> their own placeholder names to avoid any conflicts:
>
>     (sql-execute
>      "INSERT INTO foo VALUES ({myval1}::int, {myval2}::int)"
>      '((myval1 . 1) (myval2 . 2)))

Yeah, I would go for the single braces, there's a lot of duplicate things
in this query already.

So if we had

     (sql-execute
      "INSERT INTO foo VALUES ({myval1}, '{myval2}'::text[])"
      '((myval1 . 1)))

this would simply insert a tuple of 1 and the array with the single
string value 'myval2' into the table, right?  Because myval2 is not
supplied, it gets converted to

   "INSERT INTO foo VALUES ($1, '{myval2}::text[])".

or in MySQL:

   "INSERT INTO foo VALUES (?, '{myval2}::text[])".

I could see some problems with this if the user would incorrectly quote
their expressions.  In the case of

     (sql-execute
      "INSERT INTO foo VALUES ('{myval1}', '{myval2}'::text[])"
      '((myval1 . "hi there")))

it would get translated into the following, because we're doing "dumb"
replacement:

   "INSERT INTO foo VALUES ('$1', '{myval2}::text[])"

or in MySQL:

   "INSERT INTO foo VALUES ('?', '{myval2}::text[])"

With a positional parameter of 'hi there', which will hopefully give
an error message.

Cheers,
Peter