I am making function and one of my feature is make use of SQL IN operator. Basically I would like query like this
select name
from atlas_ins_th_travel_place pp
where pp.name IN ('Guyana', 'Mexico');
Then I make function and accept varchar[] as an input like this
CREATE OR REPLACE FUNCTION test_place(
places VARCHAR[]
) RETURNS SETOF test_place_view AS
$$
DECLARE
dummy ALIAS FOR $1;
BEGIN
-- FOR i IN 1 .. array_upper(places, 1)
-- LOOP
-- RAISE NOTICE '%', places[i]; -- single quotes!
-- array_append(dummy, places[i])
-- END LOOP;
RETURN QUERY
select name
from atlas_ins_th_travel_place
where name in places;
END;
$$ LANGUAGE plpgsql STABLE;
Unfortunately it raises the error regarding operator. I have tried making new array and use with it. But it does not help
Question:
How to use IN operator with array?
You need to use operator ANY for this. IN works with Lists, ANY works for Arrays
select name from table where name = ANY(places)
Operator IN supports only lists. It doesn't support searching in a array. You should to use operator = ANY().
postgres=# select 10 = ANY(ARRAY[10,20,30]);
┌──────────┐
│ ?column? │
╞══════════╡
│ t │
└──────────┘
(1 row)
postgres=# select 11 = ANY(ARRAY[10,20,30]);
┌──────────┐
│ ?column? │
╞══════════╡
│ f │
└──────────┘
(1 row)
or maybe you can use a function array_position (but there a statistics will not be used):
postgres=# select array_position(ARRAY[10,2,3], 10);
┌────────────────┐
│ array_position │
╞════════════════╡
│ 1 │
└────────────────┘
(1 row)
postgres=# select array_position(ARRAY[10,2,3], 11);
┌────────────────┐
│ array_position │
╞════════════════╡
│ ∅ │
└────────────────┘
(1 row)
If a array is small (< 100), then is not too important, what you use. For larger arrays probably better is using a operator. But it depends on the context.