$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
+
+DECLARE
+ ou INT;
+ org_unit_depth INT;
+ ou_parent INT;
+ parent_depth INT;
+ errors_found BOOLEAN;
+ ou_shortname TEXT;
+ parent_shortname TEXT;
+ ou_type_name TEXT;
+ parent_type TEXT;
+
+BEGIN
+
+ errors_found := FALSE;
+
+ FOR ou IN EXECUTE ('SELECT DISTINCT id FROM actor.org_unit ORDER BY 1;') LOOP
+
+ SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
+ SELECT depth FROM actor.org_unit_type WHERE id = ou INTO org_unit_depth;
+ SELECT depth FROM actor.org_unit_type WHERE id = ou_parent INTO parent_depth;
+
+ IF ou_parent IS NOT NULL AND org_unit_depth - parent_depth <> 1 THEN
+ SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
+ SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
+ SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name;
+ SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type;
+ RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
+ ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
+ errors_found := TRUE;
+ END IF;
+
+ END LOOP;
+
+ IF NOT errors_found THEN
+ RAISE INFO 'No errors found.';
+ END IF;
+
+ RETURN;
+
+END;
+
+$$ LANGUAGE plpgsql;