1 --Upgrade Script for 2.3.5 to 2.3.6
2 \set eg_version '''2.3.6'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.3.6', :eg_version);
5 -- Evergreen DB patch XXXX.function.axis_authority_tags_refs_aggregate.sql
8 -- check whether patch can be applied
9 SELECT evergreen.upgrade_deps_block_check('0784', :eg_version);
11 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
12 SELECT ARRAY_AGG(y) from (
13 SELECT unnest(ARRAY_CAT(
15 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
17 FROM authority.browse_axis_authority_field_map a
21 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
22 SELECT ARRAY_AGG(y) from (
23 SELECT unnest(ARRAY_CAT(
24 ARRAY[a.authority_field],
25 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
27 FROM authority.control_set_bib_field a
31 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
32 SELECT ARRAY_AGG(y) from (
33 SELECT unnest(ARRAY_CAT(
35 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
37 FROM authority.control_set_authority_field a
43 INSERT INTO config.upgrade_log (version) VALUES ('0787');
45 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
47 acsaf authority.control_set_authority_field%ROWTYPE;
56 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
58 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
61 SELECT control_set INTO cset
62 FROM authority.control_set_authority_field
63 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
67 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
68 IF thes_code IS NULL THEN
70 ELSIF thes_code = 'z' THEN
71 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
75 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
76 tag_used := acsaf.tag;
77 nfi_used := acsaf.nfi;
79 FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
80 tmp_text := oils_xpath_string('//*[@tag="'||tag_used||'"]/*[@code="'||sf||'"]', marcxml);
82 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
84 tmp_text := SUBSTRING(
89 oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml),
104 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
105 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
108 EXIT WHEN heading_text <> '';
111 IF heading_text <> '' THEN
112 IF no_thesaurus IS TRUE THEN
113 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
115 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
118 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
122 $func$ LANGUAGE PLPGSQL IMMUTABLE;