Set for multiple fields in update query

Questions / Help
, ,
1

Hello!
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?

2

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

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.

4

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

5

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

6

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?

7

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

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