-- -- cr_items_tree_update_tr/0 -- create or replace function cr_items_tree_update_tr( ) returns trigger as $$ declare v_parent_sk varbit default null; v_max_child_sortkey varbit; v_parent_id integer default null; v_old_parent_length integer; begin if new.item_id = old.item_id and ((new.parent_id = old.parent_id) or (new.parent_id is null and old.parent_id is null)) then return new; end if; select item_id into v_parent_id from cr_items where item_id = new.parent_id; -- the tree sortkey is going to change so get the new one and update it and all its -- children to have the new prefix... v_old_parent_length := length(new.tree_sortkey) + 1; if new.parent_id = 0 then v_parent_sk := int_to_tree_key(new.item_id+1000); elsif v_parent_id is null then v_parent_sk := int_to_tree_key(new.parent_id+1000) || int_to_tree_key(new.item_id+1000); else SELECT tree_sortkey, tree_increment_key(max_child_sortkey) INTO v_parent_sk, v_max_child_sortkey FROM cr_items WHERE item_id = new.parent_id FOR UPDATE; UPDATE cr_items SET max_child_sortkey = v_max_child_sortkey WHERE item_id = new.parent_id; v_parent_sk := v_parent_sk || v_max_child_sortkey; end if; UPDATE cr_items SET tree_sortkey = v_parent_sk || substring(tree_sortkey, v_old_parent_length) WHERE tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey); return new; end;$$ language plpgsql;