Support for Composite Struct types in Postgres

[tovbinm]

Howdy folks. I have a bit of a long question on how to handle struct types with Doobie & Postrgres.

Assume the following toy schema:

CREATE TYPE public."Payment" AS ("cardId" character varying, "expiration" character varying)
CREATE TABLE public."User" AS (email character varying NOT NULL, payment public."Payment")

Represented with classes:

case class Payment(cardId: String, expiration: String)
case class User(email: String, payment: Option[Payment])

Then when writing a query:

sql"""INSERT INTO public."User" (email, payment) VALUES (${user.email}, ${user.payment})"""

I get and error that Put and Get instances are not found in scope. So I tried providing one as follows and the only way I made it work is with Put and Get for Advanced.one as follows:

implicit val pGet: Get[Payment] =
    Get.Advanced
      .one[PGobject](
        JdbcType.Struct,
        NonEmptyList.of("public.\"Payment\""),
        (rs, n) => {
          rs.getObject(n) match {
            case null        => null
            case a: PGobject => a
            case a           => throw InvalidObjectMapping(classOf[PGobject], a.getClass)
          }
        }
      ).temap[Payment] { o =>
         Option(o.getValue).map(_.stripPrefix("(").stripSuffix(")").split(',')) match {
           case Some(Array(cardId, expiration)) =>
               Payment(cardId= cardId,expiration= expiration)
           case _ =>
               throw InvalidValue[PGobject, PermissionsBoundary](o, s"Failed to read '$permissionsBoundarySchemaType'")
      }
    }

  implicit val pPut: Put[Payment] =
    Put.Advanced
      .one[PGobject](
        JdbcType.Struct,
        NonEmptyList.of("public.\"Payment\""),
        (ps, n, a) => ps.setObject(n, a),
        (rs, n, a) => rs.updateObject(n, a)
      ).tcontramap[Payment] { v =>
        val o = new PGobject
        o.setType("public.\"Payment\"")
        o.setValue(s"(${v.cardId},${v.expiration})")
        o
      }

Is there a better way to write it? Is it possible to provide a better support for Composite Struct type for at least Scala Product types and Java POJO?

Thank you in advance!

[szymon-rd]

I've run into similar problem. Are there any updates on this matter?

[satorg]

Looks similar to this one: #278 But seems there's still no direct solution yet.