Using RPostgres, what should I use to "set role..." for the table I will write to a db?

Question

I'm new to connecting to databases via R, and I am trying to find best practices to minimize errors and problems. I am uploading a table from R to a postgres database, and I need to set the permissions to a certain group that I know the name of.

I'm trying to figure out the different behaviors and best practices for various DBI functions, so that I don't accidentally make a mistake and mess up the database.

I don't know whether I should use dbExecute() or dbSendQuery(). I've read the R documentation for both functions, and understand that they execute sql commands to modify the connected database. I understand that dbExecute() tells me the number of rows affected, but dbSendQuery() seems to also. dbExecute() seems to use dbSendStatement(), but this does not help me understand the difference because it seems similar.

I can't explain the behavior I see in these two examples below. Are they both doing the same thing? Are they both working? Is one way better or safer than the other?

Example 1

res <- dbExecute(con,'set role certain_group')
print(res)          # output is: [1] 0
dbClearResult(res)  # output is: Error in (function (classes, fdef, mtable)  : 
#  unable to find an inherited method for function ‘dbClearResult’ for signature ‘"integer"’

Example 2

res2 <- dbSendQuery(con,'set role certain_group')
print(res2)         # output is: <PqResult>
  SQL  set role certain_group
  ROWS Fetched: 0 [complete]
       Changed: 0
dbClearResult(res)  # no output in console

Final note: I prefer to use the RPostgres package as opposed to other options.

Answer 1

In SQL, most commands fall under two types: action queries that affect data (i.e., INSERT, UPDATE, DELETE, DROP) or resultset queries that return data (i.e., SELECT).

In R's DBI, different methods trigger these two types of commands per documentation:

• dbExecute is mainly used for action queries and is actually a wrapper for dbSendStatement + dbGetRowsAffected + dbClearResult. Per docs:

dbExecute: Execute an update statement, query number of rows affected, and then close result set

• dbGetQuery is mainly used for resultset queries migrating results to a data frame and is actually a wrapper for dbSendQuery + dbFetch + dbClearResult. Per docs:

dbGetQuery: Send query, retrieve results and then clear result set

With that said, both dbExecute and dbSendQuery should run any type of SQL statement but their return values differ. Depending on the package flavor (i.e., odbc, ROracle, RMySQL, RPostgreSQL), you may need to use dbSendQuery to run action statements particularly for binding parameters with dbBind. But dbExecute will never return a data frame!

Your Postgres-specific SET statement is a special action query. Therefore, simply call dbExecute to run and retrieve any rows affected. Alternatively, call dbSendQuery + dbGetRowsAffected + dbClearResult to possibly achieve the same result as dbExecute.