How to execute sql query files via RPostgreSQL

问题:

I am accessing my PostgreSQL database (9.3) via R using the RPostgreSQL package. I have a few very long and big sql queries (several MB big. generated from raster2pgsql).

How can I send / execute sql query files as statement within R?

The normal way

\i query.sql

does not seem to work via dbSendQuery. I tried to read in the whole sql file as character vector via readLines, however this also fails, because dbSendQuery only supports a single command apparently?

回答1:

dbSendQuery or dbGetQuery is just for the "SQL" part, not the psql commands such as \i.
In your case the simplest is indeed to use readLines but then wrap dbGetQuery in a sapply call.

con <- dbConnect(...)          #Fill this as usual
queries <- readLines("query.sql")
sapply(queries, function(x) dbGetQuery(con,x))
dbDisconnect(con)

Since I use this very often, I have a shortcut for this in my .Rprofile file:

dbGetQueries<-function(con,queries)sapply(queries,function(x)dbGetQuery(con,x))

Of course, you can also go the system way:

system("psql -U username -d database -h 127.0.0.1 -p 5432 -f query.sql") #Remember to use your actual username, database, host and port