1 -- Evergreen DB patch XXXX.handle_null_svf_during_import.sql
3 -- Prevent applying a normalization function to a null SVF
4 -- attribute value from breaking record import.
9 -- check whether patch can be applied
10 SELECT evergreen.upgrade_deps_block_check('0766', :eg_version);
12 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$
17 xfrm config.xml_transform%ROWTYPE;
19 new_attrs HSTORE := ''::HSTORE;
20 attr_def config.record_attr_definition%ROWTYPE;
23 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP
25 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
26 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(x.value), COALESCE(attr_def.joiner,' ')) INTO attr_value
27 FROM vandelay.flatten_marc(xml) AS x
28 WHERE x.tag LIKE attr_def.tag
30 WHEN attr_def.sf_list IS NOT NULL
31 THEN POSITION(x.subfield IN attr_def.sf_list) > 0
38 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
39 attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field);
41 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
43 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
45 -- See if we can skip the XSLT ... it's expensive
46 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
47 -- Can't skip the transform
48 IF xfrm.xslt <> '---' THEN
49 transformed_xml := oils_xslt_process(xml,xfrm.xslt);
51 transformed_xml := xml;
54 prev_xfrm := xfrm.name;
57 IF xfrm.name IS NULL THEN
58 -- just grab the marcxml (empty) transform
59 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
60 prev_xfrm := xfrm.name;
63 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
65 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
66 SELECT m.value::TEXT INTO attr_value
67 FROM vandelay.marc21_physical_characteristics(xml) v
68 JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
69 WHERE v.subfield = attr_def.phys_char_sf
70 LIMIT 1; -- Just in case ...
74 -- apply index normalizers to attr_value
76 SELECT n.func AS func,
77 n.param_count AS param_count,
79 FROM config.index_normalizer n
80 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
81 WHERE attr = attr_def.name
83 EXECUTE 'SELECT ' || normalizer.func || '(' ||
84 quote_nullable( attr_value ) ||
86 WHEN normalizer.param_count > 0
87 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
94 -- Add the new value to the hstore
95 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
101 $_$ LANGUAGE PLPGSQL;