How to append a string to a text column value in Postgres using Ecto

Question

I have a postgres database with a dictionaries table containing a column called body. This column has a text datatype capable of holding strings of variable unlimited length. I am attempting to iterate through lines in a large input stream, and append these lines to this column in the most recently inserted row where the kind column matches the specified arg.

I have attempted to achieve this with the following:

def append_dictionary(kind, line) do
  from(d in Dictionary, where: d.kind == ^kind)
  |> last()
  |> update([d], set: [body: d.body + ^line])
  |> Repo.update_all([])
end

but I receive the following error:

** (Ecto.QueryError) `update_all` allows only `with_cte`, `where` and `join` expressions.

I need to apply the update only to most recent row in dictionaries where the kind column matches the supplied arg. How can I achieve this?

Another important issue here is that the + operator is not supported in an Ecto query. What should I use to concatenate in the update?

Answer 1

def append_dict(kind, line) do
  q = from(dd in Dictionary, where: dd.kind == ^kind, order_by: [desc: dd.id], limit: 1)

  from(
    d in Dictionary,
    join: dd in subquery(q),
    on: d.id == dd.id,
    select: d
  )
  |> update([d], set: [body: fragment("? || ?", d.body, ^line)])
  |> Repo.update_all([])
end