BEGIN; INSERT INTO config.upgrade_log (version) VALUES ('0436'); -- miker CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$ DECLARE output vandelay.compile_profile%ROWTYPE; profile vandelay.merge_profile%ROWTYPE; profile_tmpl TEXT; profile_tmpl_owner TEXT; add_rule TEXT := ''; strip_rule TEXT := ''; replace_rule TEXT := ''; preserve_rule TEXT := ''; BEGIN profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1]; profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1]; IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN SELECT p.* INTO profile FROM vandelay.merge_profile p JOIN actor.org_unit u ON (u.id = p.owner) WHERE p.name = profile_tmpl AND u.shortname = profile_tmpl_owner; IF profile.id IS NOT NULL THEN add_rule := COALESCE(profile.add_spec,''); strip_rule := COALESCE(profile.strip_spec,''); replace_rule := COALESCE(profile.replace_spec,''); preserve_rule := COALESCE(profile.preserve_spec,''); END IF; END IF; add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),','),''); strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),','),''); replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),','),''); preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),','),''); output.add_rule := BTRIM(add_rule,','); output.replace_rule := BTRIM(replace_rule,','); output.strip_rule := BTRIM(strip_rule,','); output.preserve_rule := BTRIM(preserve_rule,','); RETURN output; END; $_$ LANGUAGE PLPGSQL; COMMIT;