Unable to find an inherited method for function 'dbClearResult' for signature '"data.frame"'

Question

I have a problem with my R program querying a PostgreSQL database. Here is the code

### Here we assume that myuser, mypassword, db, host_db and db_port
### have been defined in previous lines in the code
db_con  <- dbConnect(
    RPostgres::Postgres(),
    dbname = db,
    host = host_db,
    port = db_port,
    user = myuser,
    password = mypassword
)
###
###
### Running the query
query <- dbSendQuery(
    db_con,
    paste(
        "select t1.username, count(*) as query_cnt ",
        "from public.app_queries as t1 ",
        "group by t1.username ",
        "order by query_cnt desc ",
        "limit 50",
        sep = ""
    )
)
###
### Fetching all rows from the ResultSet by setting n = -1
result <- dbFetch(query, n = -1)
###
### . . . using result here in my code . . .
###
### Once the result is no more needed, according to the documentation
### I have to clear the ResultSet before closing the connection.
dbClearResult(result)
###
### And finally close the connection to the database.
dbDisconnect(db_con)

The problem is that dbClearResult(result) in the above code fails with the following error message:

Error in (function (classes, fdef, mtable)  : 
  unable to find an inherited method for function 'dbClearResult' for signature '"data.frame"'

I'm really beginning to learn how to connect R to PostgreSQL. What I understand from the error message is that apparently the ResultSet created in my code is a data.frame and dbClearResult doesn't like that. I checked the dbSendQuery documentation but I didn't find any specific type that has to be used to create a ResultSet.

Could you kindly make some clarification and indicate where is my mistake?

Answer 1

dbClearResult applies to the query object, not to the result of the fetch (which is a dataframe, hence the error message):

query <- dbSendQuery(db_con,...)

result <- dbFetch(query, n = -1)

dbClearResult(query)

dbDisconnect(db_con)