如何在jackc/pgx中使用“where id in”子句？

Question

pgx是否支持“where in”子句？我在另一个stackoverflow线程中发现应该使用字符串连接手动构建查询。但是我认为这有点容易出错，因为必须自己处理转义/ SQL注入等问题。

我也尝试了自己解决：

const updatePurgedRecordingsStmt = "update recordings set status = 'DELETED', deleted = now() where status <> 'DELETED' and id in ($1);"

func (r *Repository) DeleteRecordings() error {
    pool, err := r.connPool()
    if err != nil {
        return errors.Wrap(err, "cannot establish connection")
    }
    pgRecIds := &pgtype.Int4Array{}

    if err := pgRecIds.Set([]int32{int32(1), int32(2)}); err != nil {
        return errors.Wrap(err, "id conversion failed")
    }
    if _, err = pool.Exec(updatePurgedRecordingsStmt, pgRecIds); err != nil {
        return errors.Wrap(err, "update stmt failed")
    }
    return nil
}

当我执行此代码时，我却收到以下错误：

错误：绑定参数1中的二进制数据格式不正确（SQLSTATE 22P03）

我使用的版本为：

Postgres：

db=> SELECT version();
                                              version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
(1 row)

PGX：

github.com/jackc/fake v0.0.0-20150926172116-812a484cc733 h1:vr3AYkKovP8uR8AvSGGUK1IDqRa5lAAvEkZG1LKaCRc=
github.com/jackc/fake v0.0.0-20150926172116-812a484cc733/go.mod h1:WrMFNQdiFJ80sQsxDoMokWK1W5TQtxBFNpzWTD84ibQ=
github.com/jackc/pgx v3.3.0+incompatible h1:Wa90/+qsITBAPkAZjiByeIGHFcj3Ztu+VzrrIpHjL90=
github.com/jackc/pgx v3.3.0+incompatible/go.mod h1:0ZGrqGqkRlliWnWB4zKnWtjbSWbGkVEFm4TeybAXq+I=
github.com/lib/pq v1.0.0 h1:X5PMW56eZitiTeO7tKzZxFCSpbFZJtkMMooicw2us9A=
github.com/lib/pq v1.0.0/go.mod h1:5WUZQaWbwv1U+lTReE5YruASi9Al49XbQIvNi/34Woo=

- u6f6o

Answer 1

正如您已经知道的那样，IN需要一系列标量表达式，而不是一个数组；然而pgtype.Int4Array表示一个数组，而不是一系列标量表达式。

"我个人认为这种方法有些容易出错，因为您必须自己处理转义/SQL注入等问题。"

但并非如此，您可以遍历数组，构造一个参数引用字符串，将其连接到查询中，然后执行查询，同时传入带有...的数组。

var paramrefs string
ids := []interface{}{1,2,3,4}
for i, _ := range ids {
    paramrefs += `$` + strconv.Itoa(i+1) + `,`
}
paramrefs = paramrefs[:len(paramrefs)-1] // remove last ","

query := `UPDATE ... WHERE id IN (` + paramrefs + `)`
pool.Exec(query, ids...)

---

或者您可以使用ANY而不是IN。

ids := &pgtype.Int4Array{}
ids.Set([]int{1,2,3,4})
query := `UPDATE ... WHERE id = ANY ($1)`
pool.Exec(query, ids)

（在这里，您可能需要将 param 引用转换为适当的数组类型，我不确定，可以尝试不使用转换，如果不行，请尝试使用转换）

- mkopriva

---

谢谢你的回答！我不得不将这些ID转换为[]int32，否则Int4Array会返回一个转换错误。 - u6f6o

你可以使用Int8Array来表示[]int64。 - jbub

你能提供一个如何使用字符串的例子吗？例如：... WHERE id = ANY ('string1', 'string2', ... ); - Loveen Dyall

@LoveenDyall 就构造paramrefs而言，本质上与以下链接中的内容相同：https://go.dev/play/p/VQ8UNWZRqSQ - mkopriva

Answer 2

func prepareWhereINString(count int) string {
    var paramrefs string
    for i := 0; i < count; i++ {
        paramrefs += `$` + strconv.Itoa(i+1) + `,`
    }
    paramrefs = paramrefs[:len(paramrefs)-1] // remove last ","
    return paramrefs
}

- Superlocrian