possibility of having a Like operator inside of the IN operator

Hoping this is the right tag to ask this question. Posted it before on a couple of forums, havent got the answer yet.

Below is a query copied from the supabase website.

const { data, error } = await supabase
  .from('users')
  .select('phone_number')
  .in('phone_number', LIKE(ARRAY[@contacts]))

I want to achieve the same but I need to pass a LIKE operator inside of the IN operator with a Dart variable. Something like below, which is just an example.

const { data, error } = await supabase
  .from('users')
  .select('phone_number')
  .in('phone_number', LIKE ANY(ARRAY[@contacts]))

The contacts variable is of String type in dart and it has a format in this way

'%91960000000’,’%7780000000',’%6720000000',’%4160000000','%7780000000'

Solution

You'll need a function in this case.

create or replace function get_users(contacts text[]) returns users as $$
  select * from users where phone_number like any(contacts);
$$ language sql;

Then you can call it as:

const { data, error } = await supabase
  .rpc('get_users', {contacts: ['%91960000000', '%6720000000']})
  .select('phone_number')