r - 将 RPostgres 或 RPostgreSQL dbGetQuery() 参数传递给 IN 运算符

Question

只是想知道是否有任何方法可以通过 dbGetQuery() 的 params 将任意数量的参数传递给 IN 子句

例如，给定

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);")

我可以做这样的事情吗:

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

其中标识符是“短值列表”，即通常少于 10 个整数或字符串。我以为我可以做类似的事情:

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

但我能做的最好的是:

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

但是从一组字符串生成 ids 有点容易出错。

Answer 1

您想使用 DBI::sqlInterpolate 生成动态查询。这比不安全地将查询粘贴在一起要好。然而，它对矢量输入的效果不是很好，因此，如果您可以忍受更多的包导入，dbplyr::translate_sql 就足够了。示例:

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