CODEWITHSUNDEEP
sqlpostgresql
user2462794
user2462794

Reputation: 305

Nested for loops in postgresql

How can I nest loops in postgresql? I have to insert into a table some data based on another table

DO $$
DECLARE productAdminsCursor CURSOR IS 
    SELECT * 
    FROM public.actor_groups 
    WHERE actor_groups.type = 'PRODUCT_ADMIN';

DECLARE actorsId INTEGER;

BEGIN FOR productadmin IN productAdminsCursor LOOP

   actorsId := (SELECT actor_id 
                FROM public.users 
                WHERE users.super_admin = TRUE 
                AND users.product_id = productadmin.product_id);

  IF actorsId NOTNULL
  THEN
    INSERT INTO public.data_permission 
    (object_type, object_id, permission_level, id_based_on_permission_level, permission_flag)
    VALUES ('ACTOR_GROUP', productadmin.key,
            'ACTOR', (SELECT key 
                      FROM public.actors 
                      WHERE actors.id = actorsId),
            63); -- super admin can everything on product admin
  END IF;

END LOOP;
END;
$$;

But if select returning value into actorsId returns more than one row, it crashes with "more than one row returned by a subquery used as an expression".

Upvotes: 0

Views: 1574

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

You would simply use insert . . . select. Your code is really hard to follow, but it is something like this:

INSERT INTO public.data_permission (object_type, object_id, permission_level, id_based_on_permission_level, permission_flag)
    SELECT 'ACTOR_GROUP', ag.key,
           'ACTOR', a.key, 63
    FROM actor_groups ag JOIN
         public.users u
         ON u.product_id = pa.product_id JOIN
         public.actors a
         ON  a.id = u.actorid
    WHERE ag.type = 'PRODUCT_ADMIN' AND u.super_admin = TRUE ;

Okay, I can't quite follow the logic that you intend. The point is that you do not need cursors and you do not need a loop at all.

Upvotes: 1

Related Questions