Dynamic SQL Query in R (WHERE)

Question

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?

Answer 1

You should use paste0 instead of paste which is producing wrong results or paste(..., collapse='') which is slightly less efficient (see ?paste0 or docs here).

Also you should consider preparing your SQL statement in separated variable. In such way you can always easily check what SQL is being produced.

I would use this (and I am using this all the time):

  x   <- "Mean"
  sql <- paste0("select * from name where statistic='", x, "'")
  # print(sql)
  query1 <- dbGetQuery(con, sql)

In case I have SQL inside a function I always add debug parameter so I can see what SQL is used:

function get_statistic(x=NA, debug=FALSE) {

  sql <- paste0("select * from name where statistic='", x, "'")

  if(debug) print(sql)

  query1 <- dbGetQuery(con, sql)

  query1
}

Then I can simply use get_statistic('Mean', debug=TRUE) and I will see immediately if generated SQL is really what I expected.

Answer 2

The Problem The problem may be that you have spaces around Mean:

x <- "Mean"
s <- paste(
  "SELECT *",
  "FROM name",
  "WHERE statistic = '",x,"'")

giving:

> s
[1] "SELECT * FROM name WHERE statistic = ' Mean '"

Corrected Version Instead try:

s <- sprintf("select * from name where statistic = '%s'", x)

giving:

> s
[1] "select * from name where statistic = 'Mean'"

gsubfn You could also try this:

library(gsubfn)
fn$dbGetQuery(con, "SELECT * 
                    FROM name 
                    WHERE statistic = '$x'")