Use file as SQL?

[James David Smith]

Hi there,

A quick question. I've been using RPostgreSQL for a while now and I think it's great. But I wondered if it was possible to do something like this?

result <- dbGetQuery(con, 'myquery.sql')

What I mean by this, is instead of writing the SQL statement, can R fetch the SQL code from out of a file instead? I ask this because I typically do my SQL development using PgAdminIII, which I then save, and then copy and paste the SQL code into an R script. This would save me some time if I could just reference the SQL code itself.

Thanks

James

[Sean O'Riordain]

Hi James,

I might be missing something, but you should be able to just do:

result <- dbGetQuery(con, readLines('myquery.sql'))

Kind regards,
Sean

--
You received this message because you are subscribed to the Google Groups "RPostgreSQL Development and Discussion List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rpostgresql-d...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ian Gow]

James:

I find myself doing something quite similar to what you describe and I find myself using functions like this for that purpose:

runSQL <- function(sql_file) {

    library(RPostgreSQL)

    pg <- dbConnect(PostgreSQL())

    sql <- paste(readLines(sql_file), collapse="\n")

    rs <- dbGetQuery(pg, sql)

    dbDisconnect(pg)

}

This function does not return data, but it would be easy to tweak such that it does (though make sure to disconnect within the function if you are running the function many times).

One nice aspect of this is that escaping of backslashes, etc., happens seamlessly (at least for my use cases).

-Ian