added a find actor stat cat function, errors when it doesn't find since I use on...

diff --git a/sql/base/base.sql b/sql/base/base.sql
index e075757..49bdae2 100644 (file)
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -2561,3 +2561,24 @@ BEGIN
 END
 $func$  LANGUAGE plpgsql;
 
+DROP FUNCTION migration_tools.find_actor_stat_cat(migration_schema TEXT, owner_org_id INTEGER, stat_cat_name TEXT, depth INTEGER);
+CREATE OR REPLACE FUNCTION migration_tools.find_actor_stat_cat(migration_schema TEXT, owner_org_id INTEGER, stat_cat_name TEXT, depth INTEGER)
+  RETURNS INTEGER AS
+$func$
+DECLARE
+    f INTEGER;
+BEGIN
+    EXECUTE 'SELECT COALESCE((SELECT id FROM ' || migration_schema || '.actor_stat_cat WHERE owner IN (SELECT id FROM actor.org_unit_ancestors_distance(' || owner_org_id || ') WHERE distance <= ' || depth || ') AND name ILIKE ''' || stat_cat_name || '''),-1)' INTO f;
+
+    IF (f = -1) THEN
+        EXECUTE 'SELECT COALESCE((SELECT id FROM ' || migration_schema || '.actor_stat_cat WHERE owner IN (SELECT id FROM actor.org_unit_ancestors_distance(' || owner_org_id || ') WHERE distance <= ' || depth || ') AND name ILIKE ''' || stat_cat_name || '''),-1)' INTO f;
+    END IF;
+
+    IF (f = -1) THEN
+        RAISE 'The stat cat you was not found.';
+    ELSE
+        RETURN f;
+    END IF;
+
+END
+$func$  LANGUAGE plpgsql;