Nested for loops in postgresql

Question

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".

Your query makes no sense. Please provide sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Mar 12, 2018 at 11:31

Gordon Linoff · Accepted Answer · 2018-03-12 11:31:47Z

1

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.

answered Mar 12, 2018 at 11:31

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Nested for loops in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related