-- -- cr_items_tree_insert_tr/0 -- create or replace function cr_items_tree_insert_tr( ) returns trigger as $$ declare v_parent_sk varbit default null; v_max_child_sortkey varbit; v_parent_id integer default null; begin select item_id into v_parent_id from cr_items where item_id = new.parent_id; if new.parent_id = 0 then new.tree_sortkey := int_to_tree_key(new.item_id+1000); elsif v_parent_id is null then new.tree_sortkey := 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; new.tree_sortkey := v_parent_sk || v_max_child_sortkey; end if; return new; end;$$ language plpgsql;