Set for multiple fields in update query

I am trying to implement a similar query with Ecto.Query

    UPDATE table as t SET
      (field_a, field_b) =
        (select * from my_function(t.field_a, t.field_b, $1, $2) as (value1 uuid, value2 timestamp));

I was excepting something like this

    from t in Table,
      update: [set: [
        {"(field_a, field_b)", fragment("(select * from my_function(?, ?, ?, ?)", t.field_a, t.field_b, ^uuid, ^ts)}

but set option accepts only atom fields.

Maybe someone know how to use similar functions in update query?

You might have to use the escape hatch for this one: Ecto.Adapters.SQL — Ecto SQL v3.8.3.

Thanks. Use RAW SQL is one option but I use this update query as upsert on_conflict query in insert_all and this looks really ugly and may allow SQL injections. So I wanted use Ecto Query.

You can maybe join the results of your function and then use update: [set: […]] with those joined values.

Good advice but unfortunately insert_all with on_conflict (Upsert) doesn’t work with join in Ecto.

I agree it doesn’t look as nice but you don’t have to worry about SQL injections if you are sending the user input as query parameters

on_conflict (Upsert) doesn’t work with join in Ecto

I believe the database itself doesn’t allow FROM in on conflict queries and it’s not an Ecto limitation, but I could be wrong. Do you have an example of this kind of query that works in your database but not in Ecto?

Yes, upsert doesn’t support FROM.
My full raw SQL query looks like:

    table (field_a, field_b)
    ('', ''),
    ('', ''),
    ('', '')
ON CONFLICT (field_a, field_b) 
  UPDATE SET (field_a, field_b) = (select * from my_function(field_a, field_b, $1, $2) as (value1 uuid, value2 timestamp));