From fd2a63dbc409f859bb103abae24abfd55f324f18 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Fri, 12 Aug 2011 16:09:11 -0400 Subject: [PATCH] return only the one applicable OU setting value 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 Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/020.schema.functions.sql | 1 + .../sql/Pg/upgrade/XXXX.fix_aous_lookup.sql | 34 +++++++++++++++++++ 2 files changed, 35 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.fix_aous_lookup.sql diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql index c086a01657..5653dedc2e 100644 --- a/Open-ILS/src/sql/Pg/020.schema.functions.sql +++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql @@ -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; + EXIT; 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 index 0000000000..dc937be484 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.fix_aous_lookup.sql @@ -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; -- 2.43.2