1 -- Evergreen DB patch 0905.schema.use_current_normalize_heading.sql
3 -- LP#1415572: ensure current version of authority.normalize_heading() is in place
8 -- check whether patch can be applied
9 SELECT evergreen.upgrade_deps_block_check('0905', :eg_version);
11 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
13 acsaf authority.control_set_authority_field%ROWTYPE;
24 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
26 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
29 SELECT control_set INTO cset
30 FROM authority.control_set_authority_field
31 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
35 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
36 IF thes_code IS NULL THEN
38 ELSIF thes_code = 'z' THEN
39 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
43 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
44 tag_used := acsaf.tag;
45 nfi_used := acsaf.nfi;
48 FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
49 FOR sf_node IN SELECT unnest(oils_xpath('./*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
51 tmp_text := oils_xpath_string('.', sf_node);
52 sf := oils_xpath_string('./@code', sf_node);
54 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
56 tmp_text := SUBSTRING(
61 oils_xpath_string('./@ind'||nfi_used, tag_node),
76 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
77 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
81 EXIT WHEN heading_text <> '';
84 EXIT WHEN heading_text <> '';
87 IF heading_text <> '' THEN
88 IF no_thesaurus IS TRUE THEN
89 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
91 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
94 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
99 $func$ LANGUAGE PLPGSQL STABLE STRICT;
101 -- fix heading and simple_headings columns without
102 -- causing a full authority reingest
103 ALTER TABLE authority.record_entry DISABLE TRIGGER a_marcxml_is_well_formed;
104 ALTER TABLE authority.record_entry DISABLE TRIGGER aaa_auth_ingest_or_delete;
105 ALTER TABLE authority.record_entry DISABLE TRIGGER b_maintain_901;
106 ALTER TABLE authority.record_entry DISABLE TRIGGER c_maintain_control_numbers;
107 ALTER TABLE authority.record_entry DISABLE TRIGGER map_thesaurus_to_control_set;
109 UPDATE authority.record_entry SET id = id WHERE heading LIKE 'NOHEADING%';
113 -- These need to happen outside of the transaction to avoid this:
114 -- ERROR: cannot ALTER TABLE "record_entry" because it has pending trigger
116 ALTER TABLE authority.record_entry ENABLE TRIGGER a_marcxml_is_well_formed;
117 ALTER TABLE authority.record_entry ENABLE TRIGGER aaa_auth_ingest_or_delete;
118 ALTER TABLE authority.record_entry ENABLE TRIGGER b_maintain_901;
119 ALTER TABLE authority.record_entry ENABLE TRIGGER c_maintain_control_numbers;
120 ALTER TABLE authority.record_entry ENABLE TRIGGER map_thesaurus_to_control_set;