3 INSERT INTO config.upgrade_log (version) VALUES ('0195'); -- miker
5 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
9 match_attr vandelay.bib_attr_definition%ROWTYPE;
12 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
15 -- RAISE NOTICE 'already imported, cannot auto-overlay'
19 SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
21 IF match_count <> 1 THEN
22 -- RAISE NOTICE 'not an exact match';
26 SELECT d.* INTO match_attr
27 FROM vandelay.bib_attr_definition d
28 JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
29 JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
30 WHERE m.queued_record = import_id;
32 IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
33 -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
37 SELECT m.eg_record INTO eg_id
38 FROM vandelay.bib_match m
39 WHERE m.queued_record = import_id
46 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
50 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
59 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
61 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
63 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
64 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
66 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
67 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
68 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
69 IF exact_id IS NOT NULL THEN
70 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
75 IF exact_id IS NULL THEN
76 FOR attr IN SELECT a.* FROM vandelay.queued_bib_record_attr a JOIN vandelay.bib_attr_definition d ON (d.id = a.field) WHERE record = NEW.id AND d.ident IS TRUE LOOP
78 -- All numbers? check for an id match
79 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
80 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
81 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
85 -- Looks like an ISBN? check for an isbn match
86 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
87 FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE LOWER('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP
88 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
90 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
94 -- subcheck for isbn-as-tcn
95 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
96 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
100 -- check for an OCLC tcn_value match
101 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
102 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
103 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
107 -- check for a direct tcn_value match
108 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
109 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
112 -- check for a direct item barcode match
115 FROM biblio.record_entry b
116 JOIN asset.call_number cn ON (cn.record = b.id)
117 JOIN asset.copy cp ON (cp.call_number = cn.id)
118 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
120 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
128 $func$ LANGUAGE PLPGSQL;