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

Questions / Help
1

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
2

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

3

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

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

4

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