Cast a list of custom types as interpolated variable into Ecto fragment

I created a Postgrex Ltree extension https://hexdocs.pm/postgrex/Postgrex.Extension.html.
These queries work:

Repo.all from n in Node, where: fragment("path <@ ?", "1")
var = "1"
Repo.all from n in Node, where: fragment("path <@ ?", ^var)
Repo.all from n in Node, where: fragment("path <@ ?::ltree[]", ["1", "2"])

And this query doesn’t:

vars = ["1", "2"]
Repo.all from n in Node, where: fragment("path <@ ?::ltree[]", ^vars)
[error] Postgrex.Protocol (#PID<0.289.0>) disconnected: ** (RuntimeError) type `_ltree` can not be handled by the types module MyApp.PostgresTypes
** (RuntimeError) type `_ltree` can not be handled by the types module MyApp.PostgresTypes
    (ecto) lib/ecto/adapters/postgres/connection.ex:79: Ecto.Adapters.Postgres.Connection.prepare_execute/5
    (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6
    (ecto) lib/ecto/adapters/sql.ex:426: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4

Elixir 1.5.1
Ecto 2.2.5
Postgrex 0.13.3

1 Like

Did you ever find a solution to this? I’m bumping in to the same thing.

In case someone else comes across this, it doesn’t seem like this is currently supported by Ecto:

https://elixir-lang.slack.com/archives/C087B66BY/p1547659633099700

iirc ltree doesn’t support binary wire format, and postgrex doesn’t support non binary wire format arrays
I.e. postgrex requires elements of array to be serialized using binary
And ltree uses text format

As far as I remember, I decided to replace ltree altogether with recursive CTE queries using parent_id field. Those cost more on SELECT, but updates of hierarchy relationships is a breeze.

1 Like