From 800369fb2cc71890cc5742c753a69122ee6e3073 Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Wed, 26 Apr 2017 15:50:48 -0400 Subject: [PATCH] added a find actor stat cat function, errors when it doesn't find since I use on error stop --- sql/base/base.sql | 21 +++++++++++++++++++++ 1 files changed, 21 insertions(+), 0 deletions(-) diff --git a/sql/base/base.sql b/sql/base/base.sql index e075757..49bdae2 100644 --- 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; -- 1.7.2.5