Document how to work with Array of custom types

[jatcwang]

Expand SQL Arrays page to document how to work with List[MyEnum] instead of just List[String] when fetching and writing to Postgres array columns.

[satorg]

@jatcwang , is this feature supported already?

Last time I checked it (quite long ago though), I could only do value types directly, whereas for structures I had to pass them through JSON – back and forth.

[jatcwang]

You are right - I have updated the title to be more precise.

[jatcwang]

Potential solution https://discord.com/channels/632277896739946517/632727524434247691/1288688733029142588

  @SuppressWarnings(Array("org.wartremover.warts.Equals", "org.wartremover.warts.AsInstanceOf"))
  def putArray[A](
    schemaTypes: NonEmptyList[String],
    elementType: String,
    aToSql: A=> String
  ): Put.Advanced[Array[A]] =
    Put.Advanced.one(
      doobie.enumerated.JdbcType.Array,
      schemaTypes,
      (ps, n, a) => {
        val conn = ps.getConnection
        val arr = conn.createArrayOf(elementType, a.map(aToSql))
        ps.setArray(n, arr)
      },
      (rs, n, a) => {
        val stmt = rs.getStatement
        val conn = stmt.getConnection
        val arr = conn.createArrayOf(elementType, a.map(aToSql))
        rs.updateArray(n, arr)
      }
    )

  implicit val putArr: Put[Array[TestItem]] = 
    putArray[TestItem](
      NonEmptyList("_test_item", List()),
      "test_item",
      (i: TestItem) => itemToSql(i)
    )