Unable to pass JSON array to PostgreSQL function jsonb[] parameter using execute raw SQL

When I run the following query I get the error PostgresError: malformed array literal: "[{"data":"test batch message 1"},{"data":"test batch message 2"}]
const msgs = [
{ data: "test batch message 1" },
{ data: "test batch message 2" },
]
const result = await this.db.execute<{ send_batch: number }>(
sql`SELECT * FROM pgmq.send_batch(${this.queueName}, ${JSON.stringify(msgs)}::jsonb[], ${delay})`,
)
const msgs = [
{ data: "test batch message 1" },
{ data: "test batch message 2" },
]
const result = await this.db.execute<{ send_batch: number }>(
sql`SELECT * FROM pgmq.send_batch(${this.queueName}, ${JSON.stringify(msgs)}::jsonb[], ${delay})`,
)
1 Reply
tazmaniax
tazmaniaxOP2w ago
I've actually just found a solution after much trial and error by defining my own pgArray function that converts an array into the right format but it feels clumsy and should I expect Drizzle to be able to do this under the covers?
async sendBatch(msgs: T[], delay = 0): Promise<string[]> {
const result = await this.db.execute<{ send_batch: number }>(
sql`SELECT * FROM pgmq.send_batch(${this.queueName}, ${this.pgArray(msgs)}::jsonb[], ${delay})`,
)

return result.map((r) => String(r.send_batch))
}

private pgArray(array: unknown[]): SQL {
const items = array.map(item => `'${JSON.stringify(item)}'`).join(', ')
return sql.raw(`ARRAY[${items}]`)
}
async sendBatch(msgs: T[], delay = 0): Promise<string[]> {
const result = await this.db.execute<{ send_batch: number }>(
sql`SELECT * FROM pgmq.send_batch(${this.queueName}, ${this.pgArray(msgs)}::jsonb[], ${delay})`,
)

return result.map((r) => String(r.send_batch))
}

private pgArray(array: unknown[]): SQL {
const items = array.map(item => `'${JSON.stringify(item)}'`).join(', ')
return sql.raw(`ARRAY[${items}]`)
}
Want results from more Discord servers?
Add your server