1 -- Evergreen DB patch 0613.schema.vandelay_isxn_normalization.sql
6 -- check whether patch can be applied
7 SELECT evergreen.upgrade_deps_block_check('0613', :eg_version);
9 CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
11 ) RETURNS HSTORE AS $func$
15 ARRAY_ACCUM(tag || (COALESCE(subfield, ''))),
19 SELECT tag, subfield, ARRAY_ACCUM(value)::TEXT AS value
22 CASE WHEN tag = '020' THEN -- caseless -- isbn
23 LOWER((REGEXP_MATCHES(value,$$^(\S{10,17})$$))[1] || '%')
24 WHEN tag = '022' THEN -- caseless -- issn
25 LOWER((REGEXP_MATCHES(value,$$^(\S{4}[- ]?\S{4})$$))[1] || '%')
26 WHEN tag = '024' THEN -- caseless -- upc (other)
31 FROM vandelay.flatten_marc(record_xml)) x
32 GROUP BY tag, subfield ORDER BY tag, subfield
36 $func$ LANGUAGE PLPGSQL;
38 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow(
39 node vandelay.match_set_point
48 -- remember $1 is tags_rstore, and $2 is svf_rstore
52 IF node.tag IS NOT NULL THEN
53 caseless := (node.tag IN ('020', '022', '024'));
55 IF node.subfield IS NOT NULL THEN
56 tagkey := tagkey || node.subfield;
74 my_alias := 'n' || node.id::TEXT;
76 jrow := 'LEFT JOIN (SELECT *, ' || node.quality ||
77 ' AS quality FROM metabib.';
78 IF node.tag IS NOT NULL THEN
79 jrow := jrow || 'full_rec) ' || my_alias || ' ON (' ||
80 my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' ||
82 IF node.subfield IS NOT NULL THEN
83 jrow := jrow || ' AND ' || my_alias || '.subfield = ''' ||
84 node.subfield || '''';
86 jrow := jrow || ' AND (';
89 jrow := jrow || 'LOWER(' || my_alias || '.value) ' || op;
91 jrow := jrow || my_alias || '.value ' || op;
94 jrow := jrow || ' ANY(($1->''' || tagkey || ''')::TEXT[])))';
96 jrow := jrow || 'record_attr) ' || my_alias || ' ON (' ||
97 my_alias || '.id = bre.id AND (' ||
98 my_alias || '.attrs->''' || node.svf ||
99 ''' ' || op || ' $2->''' || node.svf || '''))';
101 INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow);