CODEWITHSUNDEEP
rpostgresql
Sam Mason
Sam Mason

Reputation: 16213

passing RPostgres or RPostgreSQL dbGetQuery() params to IN operator

just wondering if there's any way to pass an arbitrary number of parameters to an IN clause via params of dbGetQuery()

for example, given either

dbcon <- dbConnect(RPostgres::Postgres())
# or: dbcon <- dbConnect(RPostgreSQL::PostgreSQL())

dbExecute(dbcon, "CREATE TEMP TABLE foo AS SELECT i FROM generate_series(1, 10) x(i);")

can I do anything like:

dbGetQuery(dbcon, "SELECT * FROM foo WHERE i IN (1, 2, 3)")

where the identifiers is a "short list of values", i.e. normally less than 10 integers or strings. I thought I might be able to do something like:

ids <- c(1, 2, 3)
dbGetQuery(dbcon, "SELECT * FROM foo WHERE i IN $1", list(ids))

but the best I can do is:

ids <- '{1,2,3}'
dbGetQuery(dbcon, "SELECT * FROM foo WHERE i = ANY($1)", list(ids))

but then generating ids from a set of strings is somewhat error prone.

Upvotes: 1

Views: 683

Answers (1)

Chrisss
Chrisss

Reputation: 3251

You want to use DBI::sqlInterpolate for generating dynamic queries. This is better than unsafely pasting queries together. However it doesn't work great for vector inputs, so for that, dbplyr::translate_sql works well enough if you can put up with more package imports. Example:

require(DBI)
require(dbplyr)

con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")
month_input <- SQL(!!translate_sql(month.abb))
# Or if you don't like lazy-eval NSE nonsense
month_input <- dbplyr:::escape(month.abb)
print(month_input)
# <SQL> ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')

query <- sqlInterpolate(con, "SELECT * FROM colours WHERE month IN ?months",
  months = month_input)
print(query)
# <SQL> SELECT * FROM colours WHERE month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')

Upvotes: 1

Related Questions