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!
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)
