create constant temporary table out of a meta instance

[ShahOdin]

Postgress allows creation of constant (temporary) tables for use in queries with this syntax:

WITH names (id, name) AS (VALUES (1, 'ABC'), (2, 'BCD')) 
  SELECT id FROM names

or the inlined version:

SELECT id FROM (VALUES (1, 'ABC'), (2, 'BCD')) AS t (id, name);

assuming I have an instance of a Meta[T], is there a way of writing a generic function that takes NonEmptyList[T] and creates me a table of T values.

[guymers]

You could do something like the below:

def values[T](name: String, vs: NonEmptyList[T])(implicit W: Write[T]): Fragment = {
  val names = (1 to W.length).toVector.map(i => show"arg$i")
  val values = (1 to W.length).toVector.map(_ => "?")
  val sql = show"""
    WITH ${name} ${names.mkString_("(", ",", ")")} AS (
      VALUES
        ${vs.map(_ => values.mkString_("(", ",", ")")).mkString_(",\n")}
    )
  """
  val elems = vs.toList.flatMap { t =>
    W.puts.zip(W.toList(t)).map {
      case ((p: Put[a], Nullability.NoNulls), a) => Param.Elem.Arg(a.asInstanceOf[a], p)
      case ((p: Put[a], Nullability.Nullable), a) => Param.Elem.Opt(a.asInstanceOf[Option[a]], p)
    }
  }
  Fragment(sql, elems)
}

It is a bit inefficient as Fragment will create a Write instance from elems (if you wanted a Query it could be created via Query(sql).toQuery0(vs)).

Testing

final case class Test(a: String, b: Int, c: Boolean, inner: Option[TestInner])
final case class TestInner(a: String, b: Int, c: Boolean)

val vs = NonEmptyList.of(
  Test("a", 1, true, None),
  Test("b", 2, false, TestInner("a", 0, true).some),
)
val frag = values("test", vs) ++ fr"SELECT * FROM test"
frag.query[Test].to[List].transact(xa).unsafeRunSync()

Result

List(
  Test(a,1,true,None), 
  Test(b,2,false,Some(TestInner(a,0,true)))
)

Generated SQL

LOG:  execute <unnamed>: 
  WITH test (arg1,arg2,arg3,arg4,arg5,arg6) AS (
    VALUES
      ($1,$2,$3,$4,$5,$6),
      ($7,$8,$9,$10,$11,$12)
  )
  SELECT * FROM test 
DETAIL:  parameters: $1 = 'a', $2 = '1', $3 = 't', $4 = NULL, $5 = NULL, $6 = NULL, $7 = 'b', $8 = '2', $9 = 'f', $10 = 'a', $11 = '0', $12 = 't'