-- -- acs_object_type_update_tr/0 -- create or replace function acs_object_type_update_tr( ) returns trigger as $$ declare v_parent_sk varbit default null; v_max_value integer; v_rec record; clr_keys_p boolean default 't'; begin if new.object_type = old.object_type and ((new.supertype = old.supertype) or (new.supertype is null and old.supertype is null)) then return new; end if; for v_rec in select object_type, supertype from acs_object_types where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey) order by tree_sortkey LOOP if clr_keys_p then update acs_object_types set tree_sortkey = null where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey); clr_keys_p := 'f'; end if; select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value from acs_object_types where supertype = v_rec.supertype; select tree_sortkey into v_parent_sk from acs_object_types where object_type = v_rec.supertype; update acs_object_types set tree_sortkey = tree_next_key(v_parent_sk, v_max_value) where object_type = v_rec.object_type; end LOOP; return new; end;$$ language plpgsql;