-- Evergreen DB patch XXXX.fix_cat_default_class_lookup.sql -- -- Fix LP#825303 by allowing for ancestor OUs to be checked -- when retrieving the default classification scheme. -- BEGIN; -- check whether patch can be applied SELECT evergreen.upgrade_deps_block_check('0600', :eg_version); CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$ DECLARE sortkey TEXT := ''; BEGIN sortkey := NEW.label_sortkey; IF NEW.label_class IS NULL THEN NEW.label_class := COALESCE( ( SELECT substring(value from E'\\d+')::integer FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib) ), 1 ); END IF; EXECUTE 'SELECT ' || acnc.normalizer || '(' || quote_literal( NEW.label ) || ')' FROM asset.call_number_class acnc WHERE acnc.id = NEW.label_class INTO sortkey; NEW.label_sortkey = sortkey; RETURN NEW; END; $func$ LANGUAGE PLPGSQL; COMMIT;