0

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?

2 Answers 2

2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.