Drizzle Team•4w ago

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

tazmaniaxOP•4w 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}]`)
  }

Gaming

Programming

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