3 INSERT INTO config.upgrade_log (version) VALUES ('0343'); -- miker
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;
19 -- move any 856 entries on records that have at least one MARC-mapped URI entry
20 SELECT INTO uri_count COUNT(*)
21 FROM asset.uri_call_number_map m
22 JOIN asset.call_number cn ON (m.call_number = cn.id)
23 WHERE cn.record = source_record;
27 SELECT COUNT(*) INTO counter
28 FROM oils_xpath_table(
31 'biblio.record_entry',
33 'id=' || source_record
36 FOR i IN 1 .. counter LOOP
37 SELECT '<datafield xmlns="http://www.loc.gov/MARC21/slim" tag="856">' ||
40 '<subfield code="' || subfield || '">' ||
43 regexp_replace(data,'&','&','g'),
49 ) || '</datafield>' INTO uri_datafield
50 FROM oils_xpath_table(
53 'biblio.record_entry',
54 '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
55 '//*[@tag="856"][position()=' || i || ']/*[@code]',
56 'id=' || source_record
57 ) as t(id int,subfield text,data text);
59 uri_text := uri_text || uri_datafield;
62 IF uri_text <> '' THEN
63 UPDATE biblio.record_entry
64 SET marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
65 WHERE id = target_record;
70 -- Find and move metarecords to the target record
72 FROM metabib.metarecord
73 WHERE master_record = source_record;
76 UPDATE metabib.metarecord
77 SET master_record = target_record,
79 WHERE id = metarec.id;
81 moved_objects := moved_objects + 1;
84 -- Find call numbers attached to the source ...
85 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
87 SELECT INTO target_cn *
88 FROM asset.call_number
89 WHERE label = source_cn.label
90 AND owning_lib = source_cn.owning_lib
91 AND record = target_record;
93 -- ... and if there's a conflicting one on the target ...
96 -- ... move the copies to that, and ...
98 SET call_number = target_cn.id
99 WHERE call_number = source_cn.id;
101 -- ... move V holds to the move-target call number
102 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
104 UPDATE action.hold_request
105 SET target = target_cn.id
108 moved_objects := moved_objects + 1;
113 -- ... just move the call number to the target record
114 UPDATE asset.call_number
115 SET record = target_record
116 WHERE id = source_cn.id;
119 moved_objects := moved_objects + 1;
122 -- Find T holds targeting the source record ...
123 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
125 -- ... and move them to the target record
126 UPDATE action.hold_request
127 SET target = target_record
130 moved_objects := moved_objects + 1;
133 -- Find serial records targeting the source record ...
134 FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
135 -- ... and move them to the target record
136 UPDATE serial.record_entry
137 SET record = target_record
138 WHERE id = ser_rec.id;
140 moved_objects := moved_objects + 1;
143 -- Finally, "delete" the source record
144 DELETE FROM biblio.record_entry WHERE id = source_record;
146 -- That's all, folks!
147 RETURN moved_objects;
149 $func$ LANGUAGE plpgsql;