return only the one applicable OU setting value
authorGalen Charlton <gmc@esilibrary.com>
Fri, 12 Aug 2011 20:09:11 +0000 (16:09 -0400)
committerMike Rylander <mrylander@gmail.com>
Tue, 16 Aug 2011 12:57:31 +0000 (08:57 -0400)
Correct actor.org_unit_ancestor_setting so that it returns
at most one setting value, rather than the entire set
 of values defined for the OU and its ancestors.

Signed-off-by: Galen Charlton <gmc@esilibrary.com>
Signed-off-by: Mike Rylander <mrylander@gmail.com>
Open-ILS/src/sql/Pg/020.schema.functions.sql
Open-ILS/src/sql/Pg/upgrade/XXXX.fix_aous_lookup.sql [new file with mode: 0644]

index c086a01..5653ded 100644 (file)
@@ -314,6 +314,7 @@ BEGIN
         SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
         IF FOUND THEN
             RETURN NEXT setting;
         SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
         IF FOUND THEN
             RETURN NEXT setting;
+            EXIT;
         END IF;
         SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
         EXIT WHEN cur_org IS NULL;
         END IF;
         SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
         EXIT WHEN cur_org IS NULL;
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.fix_aous_lookup.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.fix_aous_lookup.sql
new file mode 100644 (file)
index 0000000..dc937be
--- /dev/null
@@ -0,0 +1,34 @@
+-- Evergreen DB patch XXXX.fix_aous_lookup.sql
+--
+-- Correct actor.org_unit_ancestor_setting so that it returns
+-- at most one setting value, rather than the entire set
+-- of values defined for the OU and its ancestors.
+--
+BEGIN;
+
+
+-- check whether patch can be applied
+SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+
+-- FIXME: add/check SQL statements to perform the upgrade
+CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
+DECLARE
+    setting RECORD;
+    cur_org INT;
+BEGIN
+    cur_org := org_id;
+    LOOP
+        SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
+        IF FOUND THEN
+            RETURN NEXT setting;
+            EXIT;
+        END IF;
+        SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
+        EXIT WHEN cur_org IS NULL;
+    END LOOP;
+    RETURN;
+END;
+$$ LANGUAGE plpgsql STABLE ROWS 1;
+
+
+COMMIT;