BEGIN; INSERT INTO config.upgrade_log (version) VALUES ('0196'); -- miker CREATE TYPE vandelay.tcn_data AS (tcn TEXT, tcn_source TEXT, used BOOL); CREATE OR REPLACE FUNCTION vandelay.find_bib_tcn_data ( xml TEXT ) RETURNS SETOF vandelay.tcn_data AS $_$ DECLARE eg_tcn TEXT; eg_tcn_source TEXT; output vandelay.tcn_data%ROWTYPE; BEGIN -- 001/003 eg_tcn := BTRIM((oils_xpath('//*[@tag="001"]/text()',xml))[1]); IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN eg_tcn_source := BTRIM((oils_xpath('//*[@tag="003"]/text()',xml))[1]); IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN eg_tcn_source := 'System Local'; END IF; PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted; IF NOT FOUND THEN output.used := FALSE; ELSE output.used := TRUE; END IF; output.tcn := eg_tcn; output.tcn_source := eg_tcn_source; RETURN NEXT output; END IF; -- 901 ab eg_tcn := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="a"]/text()',xml))[1]); IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN eg_tcn_source := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="b"]/text()',xml))[1]); IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN eg_tcn_source := 'System Local'; END IF; PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted; IF NOT FOUND THEN output.used := FALSE; ELSE output.used := TRUE; END IF; output.tcn := eg_tcn; output.tcn_source := eg_tcn_source; RETURN NEXT output; END IF; -- 039 ab eg_tcn := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="a"]/text()',xml))[1]); IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN eg_tcn_source := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="b"]/text()',xml))[1]); IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN eg_tcn_source := 'System Local'; END IF; PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted; IF NOT FOUND THEN output.used := FALSE; ELSE output.used := TRUE; END IF; output.tcn := eg_tcn; output.tcn_source := eg_tcn_source; RETURN NEXT output; END IF; -- 020 a eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="020"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$); IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN eg_tcn_source := 'ISBN'; PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted; IF NOT FOUND THEN output.used := FALSE; ELSE output.used := TRUE; END IF; output.tcn := eg_tcn; output.tcn_source := eg_tcn_source; RETURN NEXT output; END IF; -- 022 a eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="022"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$); IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN eg_tcn_source := 'ISSN'; PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted; IF NOT FOUND THEN output.used := FALSE; ELSE output.used := TRUE; END IF; output.tcn := eg_tcn; output.tcn_source := eg_tcn_source; RETURN NEXT output; END IF; -- 010 a eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="010"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$); IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN eg_tcn_source := 'LCCN'; PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted; IF NOT FOUND THEN output.used := FALSE; ELSE output.used := TRUE; END IF; output.tcn := eg_tcn; output.tcn_source := eg_tcn_source; RETURN NEXT output; END IF; -- 035 a eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="035"]/*[@code="a"]/text()',xml))[1], $re$^.*?(\w+)$$re$, $re$\1$re$); IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN eg_tcn_source := 'System Legacy'; PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn AND NOT deleted; IF NOT FOUND THEN output.used := FALSE; ELSE output.used := TRUE; END IF; output.tcn := eg_tcn; output.tcn_source := eg_tcn_source; RETURN NEXT output; END IF; RETURN; END; $_$ LANGUAGE PLPGSQL; COMMIT;