i'd like to delete these rows from a table in a postgres function as an array parameter:

  {id1: 4, id2: 8}
  {id1: 4, id2: 9}
  {id1: 5, id2: 8}

that is, something like this:

delete from mytable 
where (id1, id2) = ANY(Array [(4,8), (4,9), (5,8)])

which doesn't work* UPDATE: yes it does, but not in supabase. see below

but i know you can do this:

delete from mytable 
where (id1, id2) in ((4,8), (4,9), (5,8))

and this:

delete from othertable 
where id = ANY(Array [1,2])

i'd love to know if anyone has insight on how to accurately combine these. i've tried every combination i can think of. maybe i'm missing something obvious, or maybe i could use a temporary table somehow?

*the error for that attempt is cannot compare dissimilar column types bigint and integer at record column 1, but both column types are definitely bigint

Update:

so actually this works fine, as demonstrated in the comments:

delete from mytable where (id1, id2) = ANY(Array [(1,3), (2,1)]);

my errors are from a bug in the supabase client that i was using to run postgres commands, i believe.

but here's a_horse_with_no_name's answer (and critical work-around thank you!) implemented in a function, putting here for posterity:

create or replace function bulk_delete (id1s bigint [], id2s bigint [])
  returns setof bigint
  language PLPGSQL
  as $$
begin
    return query WITH deleted AS (

      delete from mytable
      where (id1, id2) in (
        select id1, id2
        from unnest(id1s, id2s) as x(id1, id2)
      ) returning *

    ) SELECT count(*) FROM deleted;
end;
$$;

select bulk_delete(Array [1,1], Array [1,2]);

response:

|bulk_delete|
|-----------|
| 2         |

adding my supabase bug report for completeness

CodePudding user response：

The only way I can think of is to pass two arrays (of the same length) then unnest them both together so that you can use an IN operator

delete from the_table
where (id1, id2) in (select id1, id2
                     from unnest(array_with_id1, array_with_id2) as x(id1, id2);