Sensitive

sens.sql

DO
$do$
DECLARE
  backlog RECORD;
  backlogUsrId app.backlog_user.id%TYPE;
  users VARCHAR[] := array['usr1','usr2'];
  usr VARCHAR;
  ownerRoleId app.role.id%TYPE;
BEGIN
  CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
  SELECT id INTO ownerRoleId FROM app.role WHERE key = 'owner';

  FOR backlog IN SELECT * FROM app.backlog LOOP

    FOREACH usr IN ARRAY users
    LOOP

        SELECT id INTO backlogUsrId FROM app.backlog_user
         WHERE
           role_id = (SELECT id FROM app.role WHERE key = 'owner')
           AND
           user_id = (SELECT id FROM app.users WHERE username = usr)
           AND
           backlog_id = backlog.id;

        IF (backlogUsrId IS NULL) THEN
          RAISE NOTICE 'Add user "%" to backlog "%s"', usr, backlog.title;
          INSERT INTO backlog_user VALUES (
            public.uuid_generate_v4(),
            backlog.id,
            (SELECT id FROM app.users WHERE username = usr),
            ownerRoleId,
            now()
          );
          RAISE NOTICE 'Add new = %', backlogUsrId;
        END IF;

    END LOOP;

  END LOOP;
END
$do$;