3

I have tried several times to run parameterized SQL statements using the RPostgreSQL package in R. I was able to run parameterized statements using positional arguments, however, I have had no luck for named arguments. Is this possible in RPostgreSQL? 

library(DBI)

# Connect to Database
con = dbConnect(...) # connection secrets hidden in .Rprofile

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 Arguments
sql = "INSERT INTO products (product_no, name, price) VALUES ($1, $2, $3);"
dbExecute(con, sql, params=list(5, "Eggs", 1.99))

# Named Arguments
sql = "INSERT INTO products (product_no, name, price) VALUES ($product_no, '$name', $price);"
dbExecute(new_con, sql, params=list(product_no=6, name="TV", price=300))

dbDisconnect(con)

I have tried several variants of the parameterized SQL statement including

sql = "INSERT INTO products (product_no, name, price) VALUES ($product_no, '$name', $price);"

and 

sql = "INSERT INTO products (product_no, name, price) VALUES (:product_no, ':name', :price);"

to no avail. Is there a way to do this in RPostgreSQL or do I need to use RPostgres?

ichbinallen
  • 1,019
  • 12
  • 18
  • did you ever figure out a way to do this? I'm trying to do something similar but haven't found anything very useful – Sam Mason Dec 04 '18 at 16:08
  • @SamMason No, I found out that using named parameters is not possible with either `RPostgreSQL` or `RPostgres`. I ended up using positional parameters and building the query somewhat manually. I would recommend the `sqlInterpolate()` function. – ichbinallen Dec 04 '18 at 23:23
  • how annoying! thanks for the pointer to `sqlInterpolate` I've seen similar suggestions and it's looking like the easiest option – Sam Mason Dec 05 '18 at 14:20

0 Answers0