Updating money amount in database using `:inc`

slouchpie

@kip You might know if this is possible or not.

I have been experimenting with different ways of updating a balance field in the database that is of the usual Money type.

Consider this:

    Multi.update_all(
      multi,
      :wallet,
      fn _ ->
        from(w in Wallet,
          where: w.id == ^wallet_id,
          update: [inc: [balance: type(^amount, ^Money.Ecto.Composite.Type.cast_type())]]
        )
      end,
      []
    )

This results in an error:

     ** (Postgrex.Error) ERROR 42883 (undefined_function) operator does not exist: money_with_currency + money_with_currency
     
         query: UPDATE "wallets" AS w0 SET "balance" = w0."balance" + $1::money_with_currency WHERE (w0."id" = $2)
     
         hint: No operator matches the given name and argument types. You might need to add explicit type casts.

I have run the “aggregate functions for money” migration although I didn’t think it would help since this is +, not sum.

I think maybe this kind of syntax just isn’t possible? Can anyone confirm?

6 comments

#ex_money

0 1420 6

2022-10-27 17:38:33 UTC

Showing Posts 1 to 6

Show Best Posts
Show All (oldest first)
Show All (newest first)

kip

ex_cldr Core Team

I’ve just published ex_money_sql version 1.5.0 that adds support for the Postgres + operator for :money_with_currency types. I believe that will also support the query you described above.

To install the required functions in Postgres the readme now says:

Plus operator `+`

ex_money defines a migration generator which, when migrated to the database with mix ecto.migrate, supports the + operator for :money_with_currency columns. The steps are:

Generate the migration by executing mix money.gen.postgres.plus_operator
Migrate the database by executing mix ecto.migrate
Formulate an Ecto query to use the + operator

  iex> q = Ecto.Query.select Item, [l], type(fragment("price + price"), l.price)
  #Ecto.Query<from l0 in Item, select: type(fragment("price + price"), l0.price)>
  iex> Repo.one q
  [debug] QUERY OK source="items" db=5.6ms queue=0.5ms
  SELECT price + price::money_with_currency FROM "items" AS l0 []
  #Money<:USD, 200>]

Let me know if you have any feedback and please file any bugs on the issue tracker.

Post #1

slouchpie

What amazing timing!
Thanks @kip . You’re genuinely an idol.
I’ll test that now and write the result here.
Update: I tried it for the use case described above and it works perfectly!

Post #2

kip

ex_cldr Core Team

Good to hear! Was a fun Saturday morning puzzle for me. And always good to be reminded how amazing Postgres is!

Post #3

gordoneliel

@slouchpie @kip How were you able to integrate money into the update all inc statement? Have been trying for a while and can’t figure it out

Post #4

kip

ex_cldr Core Team

ex_money_sql is the library that implements database support for Money.t types. After that, its Postgres (or MySQL potentially or some other database) that is doing all the work.

The + operator can be overloaded in Postgres and the implementation of that for a :money_with_currency type is here

Post #5