1

I am trying to fetch data from a local Postgresql instance into R. I need to work with parameterized queries because the queries will later depend on the users input. 

res <- postgresqlExecStatement(con, "SELECT * FROM patient_set WHERE 
instance_id  = $1", c(100))
postgresqlFetch(res,n=-1)
postgresqlCloseResult(res)

dataframe = data.frame(res)
dbDisconnect(con)

Unfortunately this still gives me the following error:

Error in as.data.frame.default(x[[i]], optional = TRUE) : cannot coerce class "structure("PostgreSQLResult", package ="RPostgreSQL")" to a data.frame

I also tried switching to dbGetQuery and dbBind but didn't get it running properly. What is the best way to fetch the result of parameterized queries from Postgresql directly into an R dataframe or table?

tobhai
  • 412
  • 6
  • 13
  • Have a look here: https://stackoverflow.com/questions/20201221/how-to-use-parameters-with-rpostgresql-to-insert-data – Tim Biegeleisen Apr 30 '18 at 06:29
  • @TimBiegeleisen thanks! It worked out with dbplyr and sql(paste0(query),escape(inputvar)) to create the query. I then used dbGetQuery to fetch data as dataframe. – tobhai Apr 30 '18 at 07:27
  • if you have an answer could you elaborate and preferably add it as an Answer. It would be helpful to me and someone who is looking for similar solution. – Jio Oct 29 '18 at 10:42

0 Answers0