How to read the contents of an .sql file into an R script to run a query?

by

I’ve had trouble with reading sql files myself, and have found that often times the syntax gets broken if there are any single line comments in the sql. Since in R you store the sql statement as a single line string, if there are any double dashes in the sql it will essentially comment out any code after the double dash.

This is a function that I typically use whenever I am reading in a .sql file to be used in R.

getSQL <- function(filepath){
  con = file(filepath, "r")
  sql.string <- ""

  while (TRUE){
    line <- readLines(con, n = 1)

    if ( length(line) == 0 ){
      break
    }

    line <- gsub("\\t", " ", line)

    if(grepl("--",line) == TRUE){
      line <- paste(sub("--","/*",line),"*/")
    }

    sql.string <- paste(sql.string, line)
  }

  close(con)
  return(sql.string)
}

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. PostgreSQL “Column does not exist” but it actually does
  3. Any downsides of using data type “text” for storing strings?
  4. Query for array elements inside JSON type
  5. Calculating Cumulative Sum in PostgreSQL
  6. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  7. Is it possible to make a recursive SQL query?
  8. Change PostgreSQL columns used in views
  9. Way to try multiple SELECTs till a result is available?
  10. How to set auto increment primary key in PostgreSQL?
  11. List columns with indexes in PostgreSQL
  12. PostgreSQL IF statement
  13. Split given string and prepare case statement
  14. Column ‘mary’ does not exist
  15. Are there any way to execute a query inside the string value (like eval) in PostgreSQL?
  16. Get top row(s) with highest value, with ties
  17. Delete parent if it’s not referenced by any other child
  18. PostgreSQL wildcard LIKE for any of a list of words
  19. Query that ignore the spaces
  20. I keep getting the error “relation [TABLE] does not exist”
  21. Postgres – Function to return the intersection of 2 ARRAYs?
  22. How to store array or multiple values in one column
  23. convert Postgres geometry format to WKT
  24. Which datatype should be used for currency?
  25. COPY with dynamic file name
  26. Table or column name cannot start with numeric?
  27. PostgreSQL column ‘foo’ does not exist
  28. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  29. How to detect query which holds the lock in Postgres?
  30. PostgreSQL: top n entries per item in same table

Leave a Comment