3 INSERT INTO config.upgrade_log (version) VALUES ('0337'); -- dbs
5 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
7 moved_objects INT := 0;
8 source_cn asset.call_number%ROWTYPE;
9 target_cn asset.call_number%ROWTYPE;
10 metarec metabib.metarecord%ROWTYPE;
11 hold action.hold_request%ROWTYPE;
12 ser_rec serial.record_entry%ROWTYPE;
13 auth_link authority.bib_linking%ROWTYPE;
20 -- move any 856 entries on records that have at least one MARC-mapped URI entry
21 SELECT INTO uri_count COUNT(*)
22 FROM asset.uri_call_number_map m
23 JOIN asset.call_number cn ON (m.call_number = cn.id)
24 WHERE cn.record = source_record;
28 SELECT COUNT(*) INTO counter
29 FROM oils_xpath_table(
32 'biblio.record_entry',
37 FOR i IN 1 .. counter LOOP
38 SELECT '<datafield xmlns="http://www.loc.gov/MARC21/slim" tag="856">' ||
41 '<subfield code="' || subfield || '">' ||
44 regexp_replace(data,'&','&','g'),
50 ) || '</datafield>' INTO uri_datafield
51 FROM oils_xpath_table(
54 'biblio.record_entry',
55 '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
56 '//*[@tag="856"][position()=' || i || ']/*[@code]',
57 'id=' || source_record
58 ) as t(id int,subfield text,data text);
60 uri_text := uri_text || uri_datafield;
63 IF uri_text <> '' THEN
64 UPDATE biblio.record_entry
65 SET marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
66 WHERE id = target_record;
71 -- Find and move metarecords to the target record
73 FROM metabib.metarecord
74 WHERE master_record = source_record;
77 UPDATE metabib.metarecord
78 SET master_record = target_record,
80 WHERE id = metarec.id;
82 moved_objects := moved_objects + 1;
85 -- Find call numbers attached to the source ...
86 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
88 SELECT INTO target_cn *
89 FROM asset.call_number
90 WHERE label = source_cn.label
91 AND owning_lib = source_cn.owning_lib
92 AND record = target_record;
94 -- ... and if there's a conflicting one on the target ...
97 -- ... move the copies to that, and ...
99 SET call_number = target_cn.id
100 WHERE call_number = source_cn.id;
102 -- ... move V holds to the move-target call number
103 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
105 UPDATE action.hold_request
106 SET target = target_cn.id
109 moved_objects := moved_objects + 1;
114 -- ... just move the call number to the target record
115 UPDATE asset.call_number
116 SET record = target_record
117 WHERE id = source_cn.id;
120 moved_objects := moved_objects + 1;
123 -- Find T holds targeting the source record ...
124 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
126 -- ... and move them to the target record
127 UPDATE action.hold_request
128 SET target = target_record
131 moved_objects := moved_objects + 1;
134 -- Find serial records targeting the source record ...
135 FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
136 -- ... and move them to the target record
137 UPDATE serial.record_entry
138 SET record = target_record
139 WHERE id = ser_rec.id;
141 moved_objects := moved_objects + 1;
144 -- Find and move authority->bib links to the target record
145 FOR auth_link IN SELECT * FROM authority.bib_linking WHERE bib = source_record LOOP
146 UPDATE authority.bib_linking
147 SET bib = target_record
148 WHERE id = auth_link.id;
150 moved_objects := moved_objects + 1;
153 -- Finally, "delete" the source record
154 DELETE FROM biblio.record_entry WHERE id = source_record;
156 -- That's all, folks!
157 RETURN moved_objects;
159 $func$ LANGUAGE plpgsql;