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?

Improve this question
3
  • Have a look here: stackoverflow.com/questions/20201221/… Commented Apr 30, 2018 at 6: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. Commented Apr 30, 2018 at 7: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. Commented Oct 29, 2018 at 10:42
Related questions
0
generating sql statements from data returned in R by Postgresql
8
Is it possible to read a data.table from PostgreSQL?
0
sending data from R to postgreSQL
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.