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; 
$$