Typecasting tuple results with Ecto

Related but different from Ecto IN clauses with tuples

Is there any way to specify a type for an expression that returns what Postgrex describes as “Anonymous composite types” and decodes to tuples?

This arose in an Ecto fragment:

fragment("array_agg((?, ?))", o.id, o.inserted_at)

which produces an array of {integer, datetime} tuples. The inserted_at values are returned as NaiveDateTime because Postgrex decodes them that way.

Trying to fix that with Ecto.Query.API.type/2 fails:

# Aspirational API - does not work like this
type(fragment("array_agg((?, ?))", o.id, o.inserted_at), {:array, {:integer, :utc_datetime_usec}})

with this error message:

(ArgumentError) unsupported type `{:integer, :utc_datetime_usec}`. The type can either be an atom, a string or a tuple of the form `{:map, t}` or `{:array, t}` where `t` itself follows the same conditions.

and stacktrace:

(ecto_sql 3.5.3) lib/ecto/adapters/postgres/connection.ex:1286: Ecto.Adapters.Postgres.Connection.ecto_to_db/1
(ecto_sql 3.5.3) lib/ecto/adapters/postgres/connection.ex:723: Ecto.Adapters.Postgres.Connection.tagged_to_db/1
(ecto_sql 3.5.3) lib/ecto/adapters/postgres/connection.ex:698: Ecto.Adapters.Postgres.Connection.expr/3
(ecto_sql 3.5.3) lib/ecto/adapters/postgres/connection.ex:1238: Ecto.Adapters.Postgres.Connection.intersperse_map/4
(ecto_sql 3.5.3) lib/ecto/adapters/postgres/connection.ex:319: Ecto.Adapters.Postgres.Connection.select/3
(ecto_sql 3.5.3) lib/ecto/adapters/postgres/connection.ex:113: Ecto.Adapters.Postgres.Connection.all/2
(ecto_sql 3.5.3) lib/ecto/adapters/postgres.ex:102: Ecto.Adapters.Postgres.prepare/2
(ecto 3.5.5) lib/ecto/query/planner.ex:180: Ecto.Query.Planner.query_without_cache/4
(ecto 3.5.5) lib/ecto/query/planner.ex:150: Ecto.Query.Planner.query_prepare/6
(ecto 3.5.5) lib/ecto/query/planner.ex:125: Ecto.Query.Planner.query_with_cache/7
(ecto 3.5.5) lib/ecto/repo/queryable.ex:213: Ecto.Repo.Queryable.execute/4
(ecto 3.5.5) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
...

Support for these kinds of data has been added in limited cases (for IN clauses with literals) but I couldn’t find any discussion of typecasting. Has this come up before?

If this feature did exist, what would the syntax look like? A bare tuple of types looks nice, but {:array, {:tuple, [:integer, :utc_datetime_usec]}} would be more in harmony with the other keywords (:array and :map ) and avoid wrangling variable-size tuples. It would match the format already used in the planner, though there is this note about “not wanting to allow fields of this type”.

Trying that syntax in Ecto fails to even compile:

# Aspirational API - does not work like this
type(fragment("array_agg((?, ?))", o.id, o.inserted_at), {:array, {:tuple, [:integer, :utc_datetime_usec]}})

# gets the error
** (Ecto.Query.CompileError) type/2 expects an alias, atom or source.field as second argument, got: `[:integer, :utc_datetime_usec]`
    (ecto 3.5.5) expanding macro: Ecto.Query.select/3
    iex:14: (file)
    (ecto 3.5.5) expanding macro: Ecto.Query.where/3
    iex:14: (file)
    (ecto 3.5.5) expanding macro: Ecto.Query.group_by/3
    iex:14: (file)
    (ecto 3.5.5) expanding macro: Ecto.Query.limit/3
    iex:14: (file)