Array of enums

[voidcontext]

Hi Everyone,

I cannot find a way to get array of enums work in postgres:

import doobie.implicits.*
import doobie.postgres.implicits.*
import doobie.util.meta.Meta

enum Colours extends Enum[Colours]:
	case Red, Green, Blue

given Meta[Colours] = pgJavaEnum("colours")

summon[Meta[Colours]]

// These fail to compile:
summon[Meta[Array[Colours]]]
summon[Meta[List[Colours]]]

Scastie: https://scastie.scala-lang.org/voidcontext/uyKosN1iR4eEAO6SLPkhxw/1

According to this quite old issue, this is not supported by doobie. Is that still the case?

[jatcwang]

I haven't looked into it. I'd suggest looking into what pg-jdbc's getArray and check the type of element in the array.

Alternatively I think you can cast it in SQL e.g. select my_enum_arr :: string[] from my_table and use pgEnumString? 🤔

[francesconero]

I'm not sure how correct this is in the general case, but the following Meta seems to work without needing to cast it at the query level. You can then get it back to your specific enum Scala type by translating it from and to String with imap or similar (as it's basically now done for the single valued enum column type, where there are a bunch of utility functions that go back and forth between all the enumeration Java/Scala types and String).

This is a very simple implementation, and nulls in the array will break it, as it needs the usual lift/unlift treatment other array Metas get.

def enumArrayPartialMeta(elementType: String): Meta[Array[String]] =
      Meta.Advanced.one(
        JdbcType.Array,
        NonEmptyList.of(s"$elementType[]", s"_$elementType"),
        (r, n) => {
          val a = r.getArray(n)
          (if (a == null) null else a.getArray).asInstanceOf[Array[String]]
        },
        (ps, n, a) => {
          val conn = ps.getConnection
          val arr =
            conn.createArrayOf(elementType, a.asInstanceOf[Array[AnyRef]])
          ps.setArray(n, arr)
        },
        (rs, n, a) => {
          val stmt = rs.getStatement
          val conn = stmt.getConnection
          val arr =
            conn.createArrayOf(elementType, a.asInstanceOf[Array[AnyRef]])
          rs.updateArray(n, arr)
        }
      )

Edit: Added a vendorType to make Doobie's check also work. Seems a bit like a hack though.