--
-- 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;