Encode array of ecto type that maps to a postgres composite type

Questions / Help
,
1

I have an Ecto.Type Size that maps to postgres composite type size.
I can encode a Size parameter but can’t encode an array of Size.

Here some details, the postgres type

CREATE TYPE size AS (value numeric, unit varchar(255))

The Ecto.Type has a basic implementation, type/1, cast/1, dump/1 and load/1 (I omit the details).

Encode a Size parameter works (A):

> q = from(d in Display, where: d.size == type(^s43, Size) and d.id == 1440, select: d.id); Repo.to_sql(:all, q)
{"SELECT d0.\"id\" FROM \"displays\" AS d0 WHERE ((d0.\"size\" = $1::size) AND (d0.\"id\" = 1440))",
 [{43, "inch"}]}
> Repo.all(q)
[1440]

But encode an array of Size does not (B):

q = from(d in Display, where: d.size in type(^[{43, "inch"}], {:array, Size}) and d.id == 1440, select: d.id); Repo.to_sql(:all, q)
{"SELECT d0.\"id\" FROM \"displays\" AS d0 WHERE (d0.\"size\" = ANY($1) AND (d0.\"id\" = 1440))",
 [[{43, "inch"}]]}

Repo.all(q)
** (DBConnection.EncodeError) cannot encode anonymous tuple {43, "inch"}. Please define a custom Postgrex extension that matches on its underlying type:
    use Postgrex.BinaryExtension, type: "typeinthedb"

It looks like some typing was lost there, when I use {:array, Size}, I want to mean size[].

Note that provide explicit cast of param to size[] works, at the sql level:

Repo.query!("select id from displays where size=any($1::size[]) and id=1440", [[{43, "inch"}]]).rows
[[1440]]

Is there something missing in my ecto expression (B)?

2 Likes
2

A containment is fragment:

iex(5)> q = from(d in Display, where: d.id == 1440, select: d.id)
#Ecto.Query<from d0 in PhenixDisplays.Displays.Display, where: d0.id == 1440,
 select: d0.id>
iex(6)> q = where(q, [d], fragment("?=ANY(?)", d.size, type(^[size], {:array, Size}))); Repo.to_sql(:all, q)
{"SELECT d0.\"id\" FROM \"displays\" AS d0 WHERE (d0.\"id\" = 1440) AND (d0.\"size\"=ANY($1::size[]))",
 [[{43, "inch"}]]}
iex(7)> Repo.all(q)
[1440]
3

Problem seems to be that postgrex does not know how to handle {43, "inch"} i.e. it does not map it to the size type – when it’s in a list / array that is.

You already arrived at a good workaround IMO. But let’s list some more options:

(A)

Use fragment similar to what you already did:

sizes_to_find = [
  %Size{value: 43, unit: "inch"}, 
  %Size{value: 55, unit: "inch"}
] 
# OR: sizes_to_find = [{43, "inch"}, {55, "inch"}]

query = from d in Display,
  where: fragment(
    "? = ANY(?::size[])", 
    d.size, 
    type(^sizes_to_find, {:array, Size})
  )

Repo.all(query)

I have not tested this, just going by memory and docs. Should work but, you know, famous last words.

(B)

Define a custom type through Postgrex.BinaryExtension as the error message is guiding you to do; more or less write a small extension for postgrex. You’ll have to implement a protocol and make sure the PostgreSQL extension is loaded on startup. None of these are a big problem but still, good to know what you are getting yourself into.

I’d be tempted to just try this with Ecto.Type but we’re talking the wire protocol details here, not a transparent serialization of a type that’s a composite of others that are already well-handled.

All of that being said, your escape hatch is IMO quite fine.