passing RPostgres or RPostgreSQL dbGetQuery() params to IN operator

Question

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.

Chrisss · Accepted Answer · 2018-12-04 16:11:24Z

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')

Collectives™ on Stack Overflow

passing RPostgres or RPostgreSQL dbGetQuery() params to IN operator

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related