Search code examples
sqlrpostgresqldynamic-sql

Dynamic SQL Query in R (WHERE)

I am trying out some dynamic SQL queries using R and the postgres package to connect to my DB.

Unfortunately I get an empty data frame if I execute the following statement:

    x <- "Mean"
query1 <- dbGetQuery(con, statement = paste(
  "SELECT *",
  "FROM name",
  "WHERE statistic = '",x,"'"))

I believe that there is a syntax error somewhere in the last line. I already changed the commas and quotation marks in every possible way, but nothing seems to work. Does anyone have an idea how I can construct this SQL Query with a dynamic WHERE Statement using a R variable?

Solution

  • Try this:

        require(stringi)
    stri_paste("SELECT * ",
      "FROM name ",
      "WHERE statistic = '",x,"'",collapse="")
    ## [1] "SELECT * FROM name WHERE statistic = 'Mean'"

    or use concatenate operator %+%

    "SELECT * FROM name WHERE statistic ='" %+% x %+% "'"
## [1] "SELECT * FROM name WHERE statistic ='mean'"