Search code examples
arrayspostgresqlwhere-in

How to use PostgreSQL array in WHERE IN clause?

I am trying to simplify the following SQL statement (wrapped into function for clarity).

What is a simpler/natural syntactical way to use array inside where ... in (/*array*/) clause? (without select * from unnest(...) boilerplate)

CREATE OR REPLACE FUNCTION get_items(p_ids int[])
 RETURNS SETOF text
 LANGUAGE sql
AS $$
  select t.name 
    from my_table t 
   where f.id in (select * from unnest(p_ids))
$$;
Solution

  • Don't use IN use ANY, this also removes the need to unnest

    where f.id = any (p_ids)