Used named parameters in parameterized statements

[ichbinallen]

Is it possible for RPostgres to support the use of named parameters rather than only positional parameters in parameterized queries? This is currently possible in RSQLite and might be beneficial in RPostgres as well.

For instance, perhaps the named_parameterized_statement is possible:

con = dbConnect(RPostgres::Postgres(),
                dbname = "...",
                host = "...",
                port = ...,
                user = "...",
                password = "...")
create_products_sql = 
  "
  CREATE TABLE products (
    product_no SERIAL PRIMARY KEY,
    name text,
    price numeric
  );
  "
dbExecute(con, create_products_sql)
products = data.frame(product_no = 1:4,
                      name=c("Laptop", "Milk", "Bread", "Couch"),
                      price=c(699.99, 2.49, 3.49, 299.99)
                      )
dbWriteTable(con, "products", products, append=T, row.names=F)

positional_parameterized_statement = 
"
UPDATE products
SET price=$1
WHERE product_no=1;
"
named_parameterized_statement = 
"
UPDATE products
SET price=:price
WHERE product_no=1;
"

dbExecute(con, positional_parameterized_statement, list(599.99))
dbExecute(con, named_parameterized_statement, list(price=599.99))
dbDisconnect(con)

I don't have the best development skills, but I would be willing to work on this if a more senior developer could confirm that this would be useful and give me some rough guidance.

[krlmlr]

Fully agreed, but parameters are currently handled solely by libpq.

The canonical issue is https://github.com/r-dbi/DBI/issues/52. I think we need to preprocess the SQL ourselves (via sqlParseVariables() and friends) and from that recompose a SQL with a named parameter syntax that matches the expectations of the driver. I'm more than happy to provide further guidance if you're still interested to tackle this issue.

[krlmlr]

Rewriting queries is out of scope for DBI, we need to make do with the parameter syntax that the database or DB driver provides.