1 -- Evergreen DB patch XXXX.schema.vandelay.bib_match_isxn_caseless.sql
5 -- check whether patch can be applied
6 SELECT evergreen.upgrade_deps_block_check('0597', :eg_version);
8 CREATE INDEX metabib_full_rec_isxn_caseless_idx
9 ON metabib.real_full_rec (LOWER(value))
10 WHERE tag IN ('020', '022', '024');
13 CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
15 ) RETURNS HSTORE AS $$
19 ARRAY_ACCUM(tag || (COALESCE(subfield, ''))),
25 CASE WHEN tag IN ('020', '022', '024') THEN -- caseless
26 ARRAY_ACCUM(LOWER(value))::TEXT
28 ARRAY_ACCUM(value)::TEXT
30 FROM vandelay.flatten_marc(record_xml)
31 GROUP BY tag, subfield ORDER BY tag, subfield
37 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow(
38 node vandelay.match_set_point
47 -- remember $1 is tags_rstore, and $2 is svf_rstore
57 IF node.tag IS NOT NULL THEN
58 caseless := (node.tag IN ('020', '022', '024'));
60 IF node.subfield IS NOT NULL THEN
61 tagkey := tagkey || node.subfield;
65 my_alias := 'n' || node.id::TEXT;
67 jrow := 'LEFT JOIN (SELECT *, ' || node.quality ||
68 ' AS quality FROM metabib.';
69 IF node.tag IS NOT NULL THEN
70 jrow := jrow || 'full_rec) ' || my_alias || ' ON (' ||
71 my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' ||
73 IF node.subfield IS NOT NULL THEN
74 jrow := jrow || ' AND ' || my_alias || '.subfield = ''' ||
75 node.subfield || '''';
77 jrow := jrow || ' AND (';
80 jrow := jrow || 'LOWER(' || my_alias || '.value) ' || op;
82 jrow := jrow || my_alias || '.value ' || op;
85 jrow := jrow || ' ANY(($1->''' || tagkey || ''')::TEXT[])))';
87 jrow := jrow || 'record_attr) ' || my_alias || ' ON (' ||
88 my_alias || '.id = bre.id AND (' ||
89 my_alias || '.attrs->''' || node.svf ||
90 ''' ' || op || ' $2->''' || node.svf || '''))';
92 INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow);