1 -- Evergreen DB patch XXXX.schema.lp826844_org_unit_parent_protect_fix.sql
3 -- Correct the fact that actor.org_unit_parent_protect() may not work
4 -- due to 'IF' conditions in PL/pgSQL not necessarily processing in the
10 -- check whether patch can be applied
11 SELECT evergreen.upgrade_deps_block_check('0605', :eg_version);
13 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
15 current_aou actor.org_unit%ROWTYPE;
21 seen_ous := ARRAY[NEW.id];
23 IF (TG_OP = 'UPDATE') THEN
24 IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
25 RETURN NEW; -- Doing an UPDATE with no change, just return it
30 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
31 RETURN NEW; -- No loop. Carry on.
33 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
34 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
37 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
38 seen_ous := seen_ous || current_aou.id;
39 depth_count := depth_count + 1;
40 IF depth_count = 100 THEN
41 RAISE 'OU CHECK TOO DEEP';