3 INSERT INTO config.upgrade_log (version) VALUES ('0349'); -- 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"' ||
39 ' ind1="' || FIRST(ind1) || '"' ||
40 ' ind2="' || FIRST(ind2) || '">' ||
43 '<subfield code="' || subfield || '">' ||
46 regexp_replace(data,'&','&','g'),
52 ) || '</datafield>' INTO uri_datafield
53 FROM oils_xpath_table(
56 'biblio.record_entry',
57 '//*[@tag="856"][position()=' || i || ']/@ind1|' ||
58 '//*[@tag="856"][position()=' || i || ']/@ind2|' ||
59 '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
60 '//*[@tag="856"][position()=' || i || ']/*[@code]',
61 'id=' || source_record
62 ) as t(id int,ind1 text, ind2 text,subfield text,data text);
64 uri_text := uri_text || uri_datafield;
67 IF uri_text <> '' THEN
68 UPDATE biblio.record_entry
69 SET marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
70 WHERE id = target_record;
75 -- Find and move metarecords to the target record
77 FROM metabib.metarecord
78 WHERE master_record = source_record;
81 UPDATE metabib.metarecord
82 SET master_record = target_record,
84 WHERE id = metarec.id;
86 moved_objects := moved_objects + 1;
89 -- Find call numbers attached to the source ...
90 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
92 SELECT INTO target_cn *
93 FROM asset.call_number
94 WHERE label = source_cn.label
95 AND owning_lib = source_cn.owning_lib
96 AND record = target_record;
98 -- ... and if there's a conflicting one on the target ...
101 -- ... move the copies to that, and ...
103 SET call_number = target_cn.id
104 WHERE call_number = source_cn.id;
106 -- ... move V holds to the move-target call number
107 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
109 UPDATE action.hold_request
110 SET target = target_cn.id
113 moved_objects := moved_objects + 1;
118 -- ... just move the call number to the target record
119 UPDATE asset.call_number
120 SET record = target_record
121 WHERE id = source_cn.id;
124 moved_objects := moved_objects + 1;
127 -- Find T holds targeting the source record ...
128 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
130 -- ... and move them to the target record
131 UPDATE action.hold_request
132 SET target = target_record
135 moved_objects := moved_objects + 1;
138 -- Find serial records targeting the source record ...
139 FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
140 -- ... and move them to the target record
141 UPDATE serial.record_entry
142 SET record = target_record
143 WHERE id = ser_rec.id;
145 moved_objects := moved_objects + 1;
148 -- Finally, "delete" the source record
149 DELETE FROM biblio.record_entry WHERE id = source_record;
151 -- That's all, folks!
152 RETURN moved_objects;
154 $func$ LANGUAGE plpgsql;