--
-- category_tree__check_nested_ind/1
--
create or replace function category_tree__check_nested_ind(
  integer
) returns int4 as $$

declare
    p_tree_id                alias for $1;
    v_negative               numeric;
    v_order                  numeric;
    v_parent                 numeric;
begin
        select count(*) into v_negative from categories
	where tree_id = p_tree_id and (left_ind < 1 or right_ind < 1);

	if v_negative > 0 then 
           raise EXCEPTION '-20001: negative index not allowed!';
        end if;

        select count(*) into v_order from categories
	where tree_id = p_tree_id
	and left_ind >= right_ind;
	
	if v_order > 0 then 
           raise EXCEPTION '-20002: right index must be greater than left index!';
        end if;

        select count(*) into v_parent
	from categories parent, categories child
		where parent.tree_id = p_tree_id
		and child.tree_id = parent.tree_id
		and (parent.left_ind >= child.left_ind or parent.right_ind <= child.right_ind)
		and child.parent_id = parent.category_id;

	if v_parent > 0 then 
           raise EXCEPTION '-20003: child index must be between parent index!';
        end if;

        return 0;
end;
$$ language plpgsql;