参数化SQL在Postgres与ARRAY

Question

我正在运行下面的SQL，它工作正常:

const ids = book.map(({ id }) => id);
const myQuery = `
SELECT
row_to_json(s)
FROM (
SELECT
b.id, b.event_id, b.title, b.price, e.account_id
FROM
ticket_books b, event e
WHERE
b.event_id = e.id AND
b.id = ANY(ARRAY[${ids}])
) s
`;
const result = await server.pg.query(myQuery);

我想切换到预处理语句，所以我把上面的重写为:

const myQuery = `
SELECT
row_to_json(s)
FROM (
SELECT
b.id, b.event_id, b.title, b.price, e.account_id
FROM
ticket_books b, event e
WHERE
b.event_id = e.id AND
b.id = ANY(ARRAY[$1])
) s
`;
const result = await server.pg.query(myQuery, [ids]);

预编译语句版本失败:error: operator does not exist: integer = text.

有什么问题吗?

Answer 1

pg将为您创建数组。使用b.id = ANY($1)