Support passing lists as arguments for queries like `SELECT a, b WHERE a IN ($1)`

[slyrus]

I'm trying to pass two arguments to a query. One is a list to be used in the form of SELECT FOO IN ($1) while the second parameter is used just for something like BAR = $2.

Why should these two parameters need to be of the same length? A workaround is to replicate the other arguments, but this seems wrong.

[krlmlr]

DBI supports passing multiple parameter sets, e.g. params = list(1:2, 3:4) runs the query with parameters (1, 3) and (2, 4). Can you use list(SQL("'foo1', 'foo2'"), "bar") ?

[slyrus]

Right, I get that there can be multiple parameter sets, but why should they be of the same length? If I'm doing "SELECT * FROM a, b WHERE a.ID in ($!) and b.ID in ($2)", I see no reason why $1 and $2 should be of the same length. I guess I don't get what you're implying happens with, e.g., parameters (1,3) and (2,4). I would expect that this query would return rows corresponding to (1,2), (1,4), (3, 2), and (3,4) not just (1,2) and (3,4). Am I missing something?

[krlmlr]

There is currently no way to pass a list of values to be used in an IN statement. Can you use dbQuoteLiteral() and paste(collapse = ", ") to construct the query?

Regarding multiple parameter sets:

library(RPostgres)

con <- dbConnect(Postgres())

dbGetQuery(con, "SELECT $1 AS a, $2 AS b", list(1:2, 3:4))
#>   a b
#> 1 1 3
#> 2 2 4

^{Created on 2021-08-24 by the reprex package (v2.0.0)}

[slyrus]

I suppose I could, but I'd rather consider this issue to be a feature request to be able to pass lists in directly. And (forgive me as it's been a couple months), IIRC, the problem isn't that i can't pass in lists, but rather that there is a specious check that the passed in lists be of the same length.

[krlmlr]

Thanks, I've updated the issue title. The check is good, if we support lists we would be wrapping them in an extra list, and the length requirement still would be satisfied. It might well be that the client library libpq doesn't support this at all.