Passing array as an argument to PostgreSQL using HoneySQL

[piranha]

I’m trying to pass array as an argument, and the thing I found searching around the web and our codebase is something like this:

> (honeysql.core/format {:select [(honeysql.types/array ["a" "b"])]})
["SELECT ARRAY[?, ?]" "a" "b"]

This looks weird, especially if I wanted to insert a long-ish array. So I searched some more and found that there is a createArrayOf method on JDBC connection, so I can do this:

> (.createArrayOf c "integer" (into-array [1 2]))
#object[org.postgresql.jdbc.PgArray 0x3a2a0579 "{\"1\",\"2\"}"]
> (def c (jdbc/get-connection pool))
> (jdbc/execute! c ["select ?" (.createArrayOf c "integer" (into-array [1 2]))])
[{:?column? #object[org.postgresql.jdbc.PgArray 0x5c5d0086 "{1,2}"]}]

I can extend next.jdbc.prepare/SettableParameter to convert something to this PgArray and then back in ReadableColumn, but then there is a problem! There is no connection in SettableParameter, and PgArray constructor requires one. Doing (jdbc/get-connection pool) seems like a recipe for trouble, isn’t it?

So… any ideas how I can get around this?

[piranha]

Okay… I should’ve asked this earlier, I guess, since now I know this works:

> (jdbc/execute! pool ["select ?" (into-array [1 2])])
[{:?column? #object[org.postgresql.jdbc.PgArray 0x3fcc371b "{1,2}"]}]
> (q {:select [[(into-array [1 2]) :a]]})
[{:a #object[org.postgresql.jdbc.PgArray 0x4610c0ef "{1,2}"]}]
> (-> (q {:select [[(into-array [1 2]) :a]]}) first :a .getBaseTypeName)
"int8"
> (-> (q {:select [[(into-array ["a" "b"]) :a]]}) first :a .getBaseTypeName)
"varchar"

So just using into-array is enough! Cool, now only ReadableColumn needs to be extended…

[piranha]

That’s all it needs:

(extend-protocol jdbc-rs/ReadableColumn
  PgArray
  (read-column-by-label [v label]
    (vec (.getArray v)))
  (read-column-by-index [v ^ResultSetMetaData rsmeta i]
    (vec (.getArray v))))