Using IN clause in prepared statements

[igozali]

Hi! I was wondering if there's any way to do variable arguments in prepared statements, particularly to use the IN clause. For example, suppose I want to execute a query like such using PostgreSQL.jl, where the table's id column is of type TEXT:

conn = PostgreSQL.connect(...)
keys = join(map(x -> "'$x'", AbstractString["1", "2", "3"]), ',')
qstr = "SELECT * FROM Teams WHERE id IN (\$1)"
stmt = PostgreSQL.prepare(conn, qstr)
rs = PostgreSQL.execute(stmt, [keys])
# length(rs) == 0

This will return an empty result set, even if the rows with those keys exist in the database.

I looked around and found almost no solutions for this. I was wondering if this is a limitation from the PostgreSQL C library that PostgreSQL.jl uses, or if there's a workaround for this? Any insights would be appreciated; I would also be interested in contributing if I have a bit more info :)

[iamed2]

A solution would be to define type conversions for array types. One of the Python PostgreSQL libraries handles it this way. PostgreSQL can use IN on a comma-separated list of values, but also on arrays.

[igozali]

Thanks, Eric!

[iamed2]

You're welcome, thank you for the PR!