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