3 INSERT INTO config.upgrade_log (version) VALUES ('0520'); --dbs
5 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
12 uri_owner_list TEXT[];
20 -- Clear any URI mappings and call numbers for this bib.
21 -- This leads to acn / auricnm inflation, but also enables
22 -- old acn/auricnm's to go away and for bibs to be deleted.
23 FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP
24 DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id;
25 DELETE FROM asset.call_number WHERE id = uri_cn_id;
28 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
29 IF ARRAY_UPPER(uris,1) > 0 THEN
30 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
31 -- First we pull info out of the 856
34 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
35 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()|//*[@code="u"]/text()',uri_xml))[1];
36 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()|//*[@code="u"]/text()',uri_xml))[1];
37 CONTINUE WHEN uri_href IS NULL OR uri_label IS NULL;
39 -- Get the distinct list of libraries wanting to use
41 DISTINCT REGEXP_REPLACE(
43 $re$^.*?\((\w+)\).*$$re$,
49 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
54 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
56 -- look for a matching uri
57 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
58 IF NOT FOUND THEN -- create one
59 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
60 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
63 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
64 uri_owner := uri_owner_list[j];
66 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
67 CONTINUE WHEN NOT FOUND;
69 -- we need a call number to link through
70 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
72 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
73 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
74 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
77 -- now, link them if they're not already
78 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
80 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
92 $func$ LANGUAGE PLPGSQL;