operator does not exist: integer = integer[] plpgsql error

Question

I have a problem where operator does not exist, integer = integer[] error comes up when I try to perform the query

select staff
from affiliations
where orgUnit = any (select unnest(*) from get_ou(661));

The function get_ou(661) returns a array of integers. Iwas wondering why I can't use the = any to obtain the staff from any of the orgunits from the array.

Thank you for your help!

Answer 1

The ANY predicate used with subselect ensure comparing value against any value returned by subselect.

postgres=# SELECT * FROM foo_table;
┌────┬───┐
│ id │ x │
╞════╪═══╡
│  1 │ 9 │
│  2 │ 4 │
│  3 │ 1 │
│  4 │ 3 │
│  5 │ 7 │
│  6 │ 5 │
│  7 │ 3 │
│  8 │ 8 │
│  9 │ 3 │
│ 10 │ 8 │
└────┴───┘
(10 rows)

CREATE OR REPLACE FUNCTION public.foo(VARIADIC integer[])
 RETURNS integer[]
 LANGUAGE sql
AS $function$ SELECT $1 $function$

It is strange, your example is broken (but with syntax error). When I fix it, it is working:

postgres=# SELECT * FROM foo_table 
             WHERE x = ANY(SELECT unnest(v) FROM foo(3,8) g(v));
┌────┬───┐
│ id │ x │
╞════╪═══╡
│  4 │ 3 │
│  7 │ 3 │
│  8 │ 8 │
│  9 │ 3 │
│ 10 │ 8 │
└────┴───┘
(5 rows)

You should to change syntax and move from subselect to array expression (this solution should be preferred for this purpose):

postgres=# SELECT * FROM foo_table WHERE x = ANY(foo(3,8));
┌────┬───┐
│ id │ x │
╞════╪═══╡
│  4 │ 3 │
│  7 │ 3 │
│  8 │ 8 │
│  9 │ 3 │
│ 10 │ 8 │
└────┴───┘
(5 rows)