Experimenting with Postgres composite type. Here's two examples: Basic that extracts the columns and an attempt to read the values using doobie meta.

Basic.scala

//> using scala 3.4
//> using dep org.tpolecat::doobie-postgres:1.0.0-RC4

package basic

import doobie.*
import doobie.implicits.*
import cats.effect.IO
import cats.*
import cats.syntax.all.*
import cats.effect.unsafe.implicits.global
import org.postgresql.util.PGobject

//docker run --name pg-composite -e POSTGRES_PASSWORD=password -e POSTGRES_USER=postgres -p 5432:5432 -d postgres
val xa = Transactor.fromDriverManager[IO](
  driver = "org.postgresql.Driver",
  url = "jdbc:postgresql:",
  user = "postgres",
  password = "password",
  logHandler = None
)

def migration(): IO[Int] = {
  val nameV1 =
    sql"""|CREATE TYPE name_v1 AS (
          |    first    Text,
          |    last     TEXT
          |)
          |""".stripMargin.update.run

  val personTable =
    sql"""|CREATE TABLE person (
          |  id   SERIAL,
          |  name name_v1,
          |  age  SMALLINT
          |)
          |""".stripMargin.update.run

  (nameV1, personTable).mapN(_ + _).transact(xa)
}

case class NameV1(first: String, last: String)

case class Person(id: Long, name: NameV1, age: Int)

def insertPersonRaw(name:NameV1, age:Int) = {
  sql"""|insert into person (name, age)
        |values ((${name.first}, ${name.last}), ${age})
        |""".stripMargin.update.run
  //.withUniqueGeneratedKeys[Person]("id", "name.first", "name.last", "age")
}
def allPersons = {
  sql"""select id, (name).first, (name).last, age from person""".query[Person].to[List]
}

@main def run(m: Boolean): Unit = {
  val app = for {
    _ <- if (m) migration() else IO(0)
    inserts <- List(
      insertPersonRaw(NameV1("Ola", "Nordmann"), 20),
      insertPersonRaw(NameV1("Kari", "Olsen"), 20)
    ).sequence.map(_.sum).transact(xa)
    _ <- IO.println(s"inserted ${inserts}")
    persons <- allPersons.transact(xa)
    _ <- IO.println(s"Persons:\n${persons.mkString("- ", "\n- ", "")}")
  } yield ()
  app.unsafeRunSync()

}

WithMeta.scala

//> using scala 3.4
//> using dep org.tpolecat::doobie-postgres:1.0.0-RC4

package withmeta

import doobie.*
import doobie.implicits.*
import cats.effect.IO
import cats.*
import cats.syntax.all.*
import cats.effect.unsafe.implicits.global
import org.postgresql.util.PGobject

//docker run --name pg-composite -e POSTGRES_PASSWORD=password -e POSTGRES_USER=postgres -p 5432:5432 -d postgres
val xa = Transactor.fromDriverManager[IO](
  driver = "org.postgresql.Driver",
  url = "jdbc:postgresql:",
  user = "postgres",
  password = "password",
  logHandler = None
)

def migration(): IO[Int] = {
  val nameV1 =
    sql"""|CREATE TYPE name_v1 AS (
          |    first    Text,
          |    last     TEXT
          |)
          |""".stripMargin.update.run

  val personTable =
    sql"""|CREATE TABLE person (
          |  id   SERIAL,
          |  name name_v1,
          |  age  SMALLINT
          |)
          |""".stripMargin.update.run

  (nameV1, personTable).mapN(_ + _).transact(xa)
}

case class NameV1(first: String, last: String)
object NameV1 {
  // todo: support nested types and escape chars...
  given Meta[NameV1] =
    {
      given Show[PGobject] = _.getValue
      Meta.Advanced
        .other[PGobject]("name_v1")
        .tiemap[NameV1](a => {
          val rx = "^\\((.*)\\,(.*)\\)$".r
          a.getValue match {
            case rx(first, last) => Right(NameV1(first, last))
            case other => Left(s"Invalid format for 'name_v1': ${other}")
          }
        })(a => {
          val o = new PGobject()
          o.setType("name_v1")
          o.setValue(List(a.first, a.last).mkString("(", ",", ")"))
          o
        })
    }
}


case class Person(id: Long, name: NameV1, age: Int)

def insertPersonRaw(name:NameV1, age:Int) = {
  sql"""|insert into person (name, age)
        |values ($name, $age)
        |""".stripMargin.update
  .withUniqueGeneratedKeys[Person]("id", "name", "age")
}
def allPersons = {
  sql"""select id, name, age from person""".query[Person].to[List]
}

@main def run(m: Boolean): Unit = {
  val app = for {
    _ <- if (m) migration() else IO(0)
    inserts <- List(
      insertPersonRaw(NameV1("Ola", "Nordmann"), 20),
      insertPersonRaw(NameV1("Kari", "Olsen"), 20)
    ).sequence.transact(xa) //.map(_.sum)
    _ <- IO.println(s"inserted ${inserts}")
    persons <- allPersons.transact(xa)
    _ <- IO.println(s"Persons:\n${persons.mkString("- ", "\n- ", "")}")
  } yield ()
  app.unsafeRunSync()

}