postgresql `pair` function

[marcosh]

Sometimes in postgresql you need to pass a pair of value to a function.

The example I have in mind is something like array_agg ((foo.bar, foo.baz)) (the double parenthesis is required).

It would be nice to have a pair function with signature SqlExpr (Value a) -> SqlExpr (Value b) -> SqlExpr (Value (a, b)) to construct such values

[belevy]

This would require full composite type support, especially the way you are describing with your array agg. Composite types are difficult and would likely require work in persistent to support.

[fintara]

If using JSON is fine, you could do this:

import Database.Esqueleto.Internal.Internal (unsafeSqlFunction)
import Database.Esqueleto.PostgreSQL.JSON (JSONBExpr)

pair :: SqlExpr (Value a) -> SqlExpr (Value b) -> JSONBExpr (a, b)
pair = unsafeSqlFunction "jsonb_build_array"

With array_agg and nullable fields:

arrayRemoveNull $ maybeArray $ arrayAgg $ pair (a ?. Field) (b ?. Field)