How can I get the auto-generated keys in an INSERT statement using RPostgreSQL in R

I cannot find a way to get the auto-generated keys after an INSERT statement when using the R database interface to PostgreSQL (RPostgreSQL).

Is there a way to do this like in JDBC?

https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys%28%29

Solution

Eventually, I have exploited a PostgreSQL-specific SQL extension, the RETURNING clause of an INSERT statement, which returns the generated primary key(s) after executing an INSERT statement. It was also necessary to trick DBI to handle the INSERT query as if it were a SELECT query to get access to the returned values, i.e. the generated keys.

Assume we have the following table in a database: USERS(id, name), where the id is the auto-generated primary key. The following example R code snippet illustrates the concept of getting the generated key from the database, and assigning it to the R object:

query <- sprintf(
  "INSERT INTO users (name) VALUES ('%s') RETURNING id",
  users$name
)

dbres = dbGetQuery(conn, query)
users$id = dbres$id