-- Evergreen DB patch 0613.schema.vandelay_isxn_normalization.sql -- BEGIN; -- check whether patch can be applied SELECT evergreen.upgrade_deps_block_check('0613', :eg_version); CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore( record_xml TEXT ) RETURNS HSTORE AS $func$ BEGIN RETURN (SELECT HSTORE( ARRAY_ACCUM(tag || (COALESCE(subfield, ''))), ARRAY_ACCUM(value) ) FROM ( SELECT tag, subfield, ARRAY_ACCUM(value)::TEXT AS value FROM (SELECT tag, subfield, CASE WHEN tag = '020' THEN -- caseless -- isbn LOWER((REGEXP_MATCHES(value,$$^(\S{10,17})$$))[1] || '%') WHEN tag = '022' THEN -- caseless -- issn LOWER((REGEXP_MATCHES(value,$$^(\S{4}[- ]?\S{4})$$))[1] || '%') WHEN tag = '024' THEN -- caseless -- upc (other) LOWER(value || '%') ELSE value END AS value FROM vandelay.flatten_marc(record_xml)) x GROUP BY tag, subfield ORDER BY tag, subfield ) subquery ); END; $func$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow( node vandelay.match_set_point ) RETURNS VOID AS $$ DECLARE jrow TEXT; my_alias TEXT; op TEXT; tagkey TEXT; caseless BOOL; BEGIN -- remember $1 is tags_rstore, and $2 is svf_rstore caseless := FALSE; IF node.tag IS NOT NULL THEN caseless := (node.tag IN ('020', '022', '024')); tagkey := node.tag; IF node.subfield IS NOT NULL THEN tagkey := tagkey || node.subfield; END IF; END IF; IF node.negate THEN IF caseless THEN op := 'NOT LIKE'; ELSE op := '<>'; END IF; ELSE IF caseless THEN op := 'LIKE'; ELSE op := '='; END IF; END IF; my_alias := 'n' || node.id::TEXT; jrow := 'LEFT JOIN (SELECT *, ' || node.quality || ' AS quality FROM metabib.'; IF node.tag IS NOT NULL THEN jrow := jrow || 'full_rec) ' || my_alias || ' ON (' || my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' || node.tag || ''''; IF node.subfield IS NOT NULL THEN jrow := jrow || ' AND ' || my_alias || '.subfield = ''' || node.subfield || ''''; END IF; jrow := jrow || ' AND ('; IF caseless THEN jrow := jrow || 'LOWER(' || my_alias || '.value) ' || op; ELSE jrow := jrow || my_alias || '.value ' || op; END IF; jrow := jrow || ' ANY(($1->''' || tagkey || ''')::TEXT[])))'; ELSE -- svf jrow := jrow || 'record_attr) ' || my_alias || ' ON (' || my_alias || '.id = bre.id AND (' || my_alias || '.attrs->''' || node.svf || ''' ' || op || ' $2->''' || node.svf || '''))'; END IF; INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow); END; $$ LANGUAGE PLPGSQL; COMMIT;