Postgres 11 passing a list of uuids to function

Question

Im trying to create a function that accepts a list of uuids as a parameter.

My code:

create or replace function get_child_groups(input_parents_id uuid[])
returns table(id uuid, name text, parent_id uuid)
as
$$
WITH RECURSIVE c AS (
 SELECT da.*
 from device_group_v2 d
 JOIN device_group_associations da on da.parent_id = d.id
 where d.id in (input_parents_id)
 UNION ALL
 SELECT sa.*
 FROM device_group_associations AS sa JOIN c ON c.child_id = sa.parent_id)
select * from(select d.* from c join device_group_v2 d on c.parent_id = d.id OR c.child_id = d.id) as x
union
select * from device_group_v2 where id in (input_parents_id)
$$ language sql;

This fails with:

[42883] ERROR: operator does not exist: uuid = uuid[] No operator matches the given name and argument types. You might need to add explicit type casts. Position: 290

What am I doing wrong? Is a kind of typecast required there?

Answer 1

The IN operator only works with "lists" or "sets", not with arrays. You need to use the ANY operator:

where id = ANY (input_parents_id)

Answer 2

While calling function, you should either typecast proper variable to uuid[], or use uuid[] type variable.

Check below;

Passing uuid[] type variable

DO $$
 DECLARE
 param uuid[];
 rec record;
 BEGIN
 SELECT array_agg( uuidcolumn ) INTO param FROM anothertable;
 SELECT get_child_groups( param ) INTO rec;
 RAISE NOTICE '%s',rec;
 END; 
$$ 

Typecasting varchar to uuid[]

DO $$
 DECLARE
 param varchar;
 rec record;
 BEGIN
 param = '{b6edec8f-1725-4335-8470-690d1937eb2f,6bdca11b-f48b-409a-ab12-bae4bbbf4e09}';
 SELECT get_child_groups( param::uuid[] ) INTO rec;
 RAISE NOTICE '%s',rec;
 END; 
$$