2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2008 Equinox Software, Inc.
4 * Copyright (C) 2010 Laurentian University
5 * Mike Rylander <miker@esilibrary.com>
6 * Dan Scott <dscott@laurentian.ca>
8 * This program is free software; you can redistribute it and/or
9 * modify it under the terms of the GNU General Public License
10 * as published by the Free Software Foundation; either version 2
11 * of the License, or (at your option) any later version.
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
20 DROP SCHEMA IF EXISTS authority CASCADE;
23 CREATE SCHEMA authority;
25 -- subset of types listed in https://www.loc.gov/marc/authority/ad1xx3xx.html
26 -- for now, ignoring subdivisions
27 CREATE TYPE authority.heading_type AS ENUM (
37 'medium_of_performance_term'
40 CREATE TYPE authority.variant_heading_type AS ENUM (
49 CREATE TYPE authority.related_heading_type AS ENUM (
52 'parent organization',
59 CREATE TYPE authority.heading_purpose AS ENUM (
65 CREATE TABLE authority.heading_field (
66 id SERIAL PRIMARY KEY,
67 heading_type authority.heading_type NOT NULL,
68 heading_purpose authority.heading_purpose NOT NULL,
70 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mads21',
71 heading_xpath TEXT NOT NULL,
72 component_xpath TEXT NOT NULL,
73 type_xpath TEXT NULL, -- to extract related or variant type
74 thesaurus_xpath TEXT NULL,
75 thesaurus_override_xpath TEXT NULL,
79 CREATE TABLE authority.heading_field_norm_map (
80 id SERIAL PRIMARY KEY,
81 field INT NOT NULL REFERENCES authority.heading_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
82 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
84 pos INT NOT NULL DEFAULT 0
87 CREATE TYPE authority.heading AS (
89 type authority.heading_type,
90 purpose authority.heading_purpose,
91 variant_type authority.variant_heading_type,
92 related_type authority.related_heading_type,
95 normalized_heading TEXT
98 CREATE TABLE authority.control_set (
99 id SERIAL PRIMARY KEY,
100 name TEXT NOT NULL UNIQUE, -- i18n
101 description TEXT -- i18n
104 CREATE TABLE authority.control_set_authority_field (
105 id SERIAL PRIMARY KEY,
106 main_entry INT REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
107 control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
108 tag CHAR(3) NOT NULL,
109 nfi CHAR(1), -- non-filing indicator
110 sf_list TEXT NOT NULL,
111 display_sf_list TEXT NOT NULL,
112 name TEXT NOT NULL, -- i18n
113 description TEXT, -- i18n
115 linking_subfield CHAR(1),
116 heading_field INTEGER REFERENCES authority.heading_field(id)
119 CREATE TABLE authority.control_set_bib_field (
120 id SERIAL PRIMARY KEY,
121 authority_field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
125 -- Seed data will be generated from class <-> axis mapping
126 CREATE TABLE authority.control_set_bib_field_metabib_field_map (
127 id SERIAL PRIMARY KEY,
128 bib_field INT NOT NULL REFERENCES authority.control_set_bib_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
129 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
130 CONSTRAINT a_bf_mf_map_once UNIQUE (bib_field, metabib_field)
133 CREATE VIEW authority.control_set_auth_field_metabib_field_map_main AS
134 SELECT DISTINCT b.authority_field, m.metabib_field
135 FROM authority.control_set_bib_field_metabib_field_map m JOIN authority.control_set_bib_field b ON (b.id = m.bib_field);
136 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_main IS $$metabib fields for main entry auth fields$$;
138 CREATE VIEW authority.control_set_auth_field_metabib_field_map_refs_only AS
139 SELECT DISTINCT a.id AS authority_field, m.metabib_field
140 FROM authority.control_set_authority_field a
141 JOIN authority.control_set_authority_field ame ON (a.main_entry = ame.id)
142 JOIN authority.control_set_bib_field b ON (b.authority_field = ame.id)
143 JOIN authority.control_set_bib_field_metabib_field_map mf ON (mf.bib_field = b.id)
144 JOIN authority.control_set_auth_field_metabib_field_map_main m ON (ame.id = m.authority_field);
145 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_refs_only IS $$metabib fields for NON-main entry auth fields$$;
147 CREATE VIEW authority.control_set_auth_field_metabib_field_map_refs AS
148 SELECT * FROM authority.control_set_auth_field_metabib_field_map_main
150 SELECT * FROM authority.control_set_auth_field_metabib_field_map_refs_only;
151 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_refs IS $$metabib fields for all auth fields$$;
154 -- blind refs only is probably what we want for lookup in bib/auth browse
155 CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_refs_only AS
157 FROM authority.control_set_auth_field_metabib_field_map_refs_only r
158 JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
159 WHERE linking_subfield IS NULL;
160 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_blind_refs_only IS $$metabib fields for NON-main entry auth fields that can't be linked to other records$$; -- '
162 CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_refs AS
164 FROM authority.control_set_auth_field_metabib_field_map_refs r
165 JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
166 WHERE linking_subfield IS NULL;
167 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_blind_refs IS $$metabib fields for all auth fields that can't be linked to other records$$; -- '
169 CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_main AS
171 FROM authority.control_set_auth_field_metabib_field_map_main r
172 JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
173 WHERE linking_subfield IS NULL;
174 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_blind_main IS $$metabib fields for main entry auth fields that can't be linked to other records$$; -- '
176 CREATE TABLE authority.thesaurus (
177 code TEXT PRIMARY KEY, -- MARC21 thesaurus code
178 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
179 name TEXT NOT NULL UNIQUE, -- i18n
180 description TEXT, -- i18n
185 CREATE TRIGGER thes_code_tracking_trigger
186 AFTER UPDATE ON authority.thesaurus
187 FOR EACH ROW EXECUTE PROCEDURE oils_i18n_code_tracking('at');
189 CREATE TABLE authority.browse_axis (
190 code TEXT PRIMARY KEY,
191 name TEXT UNIQUE NOT NULL, -- i18n
192 sorter TEXT REFERENCES config.record_attr_definition (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
196 CREATE TABLE authority.browse_axis_authority_field_map (
197 id SERIAL PRIMARY KEY,
198 axis TEXT NOT NULL REFERENCES authority.browse_axis (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
199 field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
202 CREATE TABLE authority.record_entry (
203 id BIGSERIAL PRIMARY KEY,
204 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
205 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
206 creator INT NOT NULL DEFAULT 1,
207 editor INT NOT NULL DEFAULT 1,
208 active BOOL NOT NULL DEFAULT TRUE,
209 deleted BOOL NOT NULL DEFAULT FALSE,
211 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
213 last_xact_id TEXT NOT NULL,
218 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
219 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
220 CREATE INDEX authority_record_entry_create_date_idx ON authority.record_entry ( create_date );
221 CREATE INDEX authority_record_entry_edit_date_idx ON authority.record_entry ( edit_date );
222 CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
223 CREATE TRIGGER a_marcxml_is_well_formed BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.check_marcxml_well_formed();
224 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
225 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_control_numbers();
227 CREATE TABLE authority.authority_linking (
228 id BIGSERIAL PRIMARY KEY,
229 source BIGINT REFERENCES authority.record_entry (id) NOT NULL,
230 target BIGINT REFERENCES authority.record_entry (id) NOT NULL,
231 field INT REFERENCES authority.control_set_authority_field (id) NOT NULL
234 CREATE TABLE authority.bib_linking (
235 id BIGSERIAL PRIMARY KEY,
236 bib BIGINT NOT NULL REFERENCES biblio.record_entry (id),
237 authority BIGINT NOT NULL REFERENCES authority.record_entry (id)
239 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
240 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
242 CREATE TABLE authority.record_note (
243 id BIGSERIAL PRIMARY KEY,
244 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
246 creator INT NOT NULL DEFAULT 1,
247 editor INT NOT NULL DEFAULT 1,
248 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
249 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
251 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
252 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
253 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
255 CREATE TABLE authority.rec_descriptor (
256 id BIGSERIAL PRIMARY KEY,
262 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
264 CREATE TABLE authority.full_rec (
265 id BIGSERIAL PRIMARY KEY,
266 record BIGINT NOT NULL,
267 tag CHAR(3) NOT NULL,
272 index_vector tsvector NOT NULL
274 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
275 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
276 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
277 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
278 CREATE TRIGGER authority_full_rec_fti_trigger
279 BEFORE UPDATE OR INSERT ON authority.full_rec
280 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
282 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIN (index_vector);
283 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
284 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
285 /* But we still need this (boooo) for paging using >, <, etc */
286 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
288 CREATE RULE protect_authority_rec_delete AS ON DELETE TO authority.record_entry DO INSTEAD (UPDATE authority.record_entry SET deleted = TRUE WHERE OLD.id = authority.record_entry.id; DELETE FROM authority.full_rec WHERE record = OLD.id);
290 CREATE OR REPLACE FUNCTION authority.extract_thesaurus( marcxml TEXT ) RETURNS TEXT AS $func$
294 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
295 IF thes_code IS NULL THEN
297 ELSIF thes_code = 'z' THEN
298 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), 'z' );
300 SELECT code INTO thes_code FROM authority.thesaurus WHERE short_code = thes_code;
302 thes_code := '|'; -- default
307 $func$ LANGUAGE PLPGSQL STABLE STRICT;
309 -- Intended to be used in a unique index on authority.record_entry like so:
310 -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
311 -- ON authority.record_entry (heading)
312 -- WHERE deleted IS FALSE or deleted = FALSE;
313 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
315 acsaf authority.control_set_authority_field%ROWTYPE;
326 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
328 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
331 SELECT control_set INTO cset
332 FROM authority.control_set_authority_field
333 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
338 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
339 tag_used := acsaf.tag;
340 nfi_used := acsaf.nfi;
343 FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
344 FOR sf_node IN SELECT unnest(oils_xpath('./*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
346 tmp_text := oils_xpath_string('.', sf_node);
347 sf := oils_xpath_string('./@code', sf_node);
349 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
351 tmp_text := SUBSTRING(
356 oils_xpath_string('./@ind'||nfi_used, tag_node),
371 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
372 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
376 EXIT WHEN heading_text <> '';
379 EXIT WHEN heading_text <> '';
382 IF heading_text <> '' THEN
383 IF no_thesaurus IS TRUE THEN
384 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
386 thes_code := authority.extract_thesaurus(marcxml);
387 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
390 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
395 $func$ LANGUAGE PLPGSQL STABLE STRICT;
397 CREATE TABLE authority.simple_heading (
398 id BIGSERIAL PRIMARY KEY,
399 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
400 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
402 sort_value TEXT NOT NULL,
403 index_vector tsvector NOT NULL,
406 CREATE TRIGGER authority_simple_heading_fti_trigger
407 BEFORE UPDATE OR INSERT ON authority.simple_heading
408 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
410 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIN (index_vector);
411 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
412 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
413 CREATE INDEX authority_simple_heading_record_idx ON authority.simple_heading (record);
414 CREATE INDEX authority_simple_heading_thesaurus_idx ON authority.simple_heading (thesaurus);
416 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
418 res authority.simple_heading%ROWTYPE;
419 acsaf authority.control_set_authority_field%ROWTYPE;
420 heading_row authority.heading%ROWTYPE;
431 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
434 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
437 SELECT control_set INTO cset
438 FROM authority.control_set_authority_field
439 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
443 res.record := auth_id;
444 res.thesaurus := authority.extract_thesaurus(marcxml);
446 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
447 res.atag := acsaf.id;
449 IF acsaf.heading_field IS NULL THEN
450 tag_used := acsaf.tag;
451 nfi_used := acsaf.nfi;
452 joiner_text := COALESCE(acsaf.joiner, ' ');
454 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)::TEXT[]) LOOP
456 heading_text := COALESCE(
457 oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml, joiner_text),
461 IF nfi_used IS NOT NULL THEN
463 sort_text := SUBSTRING(
468 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
480 sort_text := heading_text;
483 IF heading_text IS NOT NULL AND heading_text <> '' THEN
484 res.value := heading_text;
485 res.sort_value := public.naco_normalize(sort_text);
486 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
492 FOR heading_row IN SELECT * FROM authority.extract_headings(marcxml, ARRAY[acsaf.heading_field]) LOOP
493 res.value := heading_row.heading;
494 res.sort_value := heading_row.normalized_heading;
495 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
503 $func$ LANGUAGE PLPGSQL STABLE STRICT;
505 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
506 SELECT authority.normalize_heading($1, TRUE);
507 $func$ LANGUAGE SQL STABLE STRICT;
509 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
510 SELECT authority.normalize_heading($1, FALSE);
511 $func$ LANGUAGE SQL STABLE STRICT;
513 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
514 Extract the authority heading, thesaurus, and NACO-normalized values
515 from an authority record. The primary purpose is to build a unique
516 index to defend against duplicated authority records from the same
520 -- Store these in line with the MARC for easier indexing
521 CREATE OR REPLACE FUNCTION authority.normalize_heading_for_upsert () RETURNS TRIGGER AS $f$
523 NEW.heading := authority.normalize_heading( NEW.marc );
524 NEW.simple_heading := authority.simple_normalize_heading( NEW.marc );
527 $f$ LANGUAGE PLPGSQL;
529 CREATE TRIGGER update_headings_tgr BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE authority.normalize_heading_for_upsert();
531 -- Adding indexes using oils_xpath_string() for the main entry tags described in
532 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
533 -- the existing index on authority.normalize_heading() helps already with a record in hand
534 CREATE OR REPLACE VIEW authority.tracing_links AS
535 SELECT main.record AS record,
537 main.tag AS main_tag,
538 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
539 substr(link.value,1,1) AS relationship,
540 substr(link.value,2,1) AS use_restriction,
541 substr(link.value,3,1) AS deprecation,
542 substr(link.value,4,1) AS display_restriction,
544 link.tag AS link_tag,
545 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
546 are.heading AS normalized_main_value
547 FROM authority.full_rec main
548 JOIN authority.record_entry are ON (main.record = are.id)
549 JOIN authority.control_set_authority_field main_entry
550 ON (main_entry.tag = main.tag
551 AND main_entry.main_entry IS NULL
552 AND main.subfield = 'a' )
553 JOIN authority.control_set_authority_field sub_entry
554 ON (main_entry.id = sub_entry.main_entry)
555 JOIN authority.full_rec link
556 ON (link.record = main.record
557 AND link.tag = sub_entry.tag
558 AND link.subfield = 'w' );
560 -- Function to generate an ephemeral overlay template from an authority record
561 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
564 main_entry authority.control_set_authority_field%ROWTYPE;
565 bib_field authority.control_set_bib_field%ROWTYPE;
566 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
568 replace_data XML[] DEFAULT '{}'::XML[];
569 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
574 IF auth_id IS NULL THEN
578 -- Default to the LoC controll set
579 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
581 -- if none, make a best guess
583 SELECT control_set INTO cset
584 FROM authority.control_set_authority_field
586 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
587 FROM authority.record_entry
593 -- if STILL none, no-op change
597 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
598 XMLELEMENT( name leader, '00881nam a2200193 4500'),
601 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
604 XMLATTRIBUTES('d' AS code),
611 FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP
612 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
613 auth_i1 = (XPATH('@ind1',auth_field[1]))[1];
614 auth_i2 = (XPATH('@ind2',auth_field[1]))[1];
615 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
616 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
617 SELECT XMLELEMENT( -- XMLAGG avoids magical <element> creation, but requires unnest subquery
619 XMLATTRIBUTES(bib_field.tag AS tag, auth_i1 AS ind1, auth_i2 AS ind2),
621 ) INTO tmp_data FROM UNNEST(XPATH('//*[local-name()="subfield"]', auth_field[1]));
622 replace_data := replace_data || tmp_data;
623 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
630 SELECT XMLAGG(UNNEST) INTO tmp_data FROM UNNEST(replace_data);
634 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
635 XMLELEMENT( name leader, '00881nam a2200193 4500'),
639 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
642 XMLATTRIBUTES('r' AS code),
643 ARRAY_TO_STRING(replace_rules,',')
648 $f$ STABLE LANGUAGE PLPGSQL;
650 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
651 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
654 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
656 moved_objects INT := 0;
658 bib_rec biblio.record_entry%ROWTYPE;
659 auth_link authority.bib_linking%ROWTYPE;
663 -- Defining our terms:
664 -- "target record" = the record that will survive the merge
665 -- "source record" = the record that is sacrifing its existence and being
666 -- replaced by the target record
668 -- 1. Update all bib records with the ID from target_record in their $0
671 FROM biblio.record_entry bre
672 JOIN authority.bib_linking abl ON abl.bib = bre.id
673 WHERE abl.authority = source_record
676 UPDATE biblio.record_entry
677 SET marc = REGEXP_REPLACE(
679 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
680 E'\\1' || target_record || '<',
683 WHERE id = bib_rec.id;
685 moved_objects := moved_objects + 1;
688 -- 2. Grab the current value of reingest on same MARC flag
689 SELECT enabled INTO ingest_same
690 FROM config.internal_flag
691 WHERE name = 'ingest.reingest.force_on_same_marc'
694 -- 3. Temporarily set reingest on same to TRUE
695 UPDATE config.internal_flag
697 WHERE name = 'ingest.reingest.force_on_same_marc'
700 -- 4. Make a harmless update to target_record to trigger auto-update
701 -- in linked bibliographic records
702 UPDATE authority.record_entry
704 WHERE id = target_record;
706 -- 5. "Delete" source_record
707 DELETE FROM authority.record_entry WHERE id = source_record;
709 -- 6. Set "reingest on same MARC" flag back to initial value
710 UPDATE config.internal_flag
711 SET enabled = ingest_same
712 WHERE name = 'ingest.reingest.force_on_same_marc'
715 RETURN moved_objects;
717 $func$ LANGUAGE plpgsql;
720 -- Support function used to find the pivot for alpha-heading-browse style searching
721 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT, thesauruses TEXT DEFAULT '' ) RETURNS TEXT AS $$
723 sort_value_row RECORD;
728 t_term := public.naco_normalize(q);
730 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
731 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
734 FROM authority.simple_heading ash
735 WHERE ash.atag = ANY (a)
736 AND ash.sort_value >= t_term
739 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
741 ORDER BY rank DESC, ash.sort_value
744 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
745 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
748 FROM authority.simple_heading ash
749 WHERE ash.atag = ANY (a)
750 AND ash.value >= t_term
753 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
755 ORDER BY rank DESC, ash.sort_value
758 IF value_row.rank > sort_value_row.rank THEN
759 RETURN value_row.sort_value;
761 RETURN sort_value_row.sort_value;
766 CREATE OR REPLACE FUNCTION authority.simple_heading_browse_center( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
768 pivot_sort_value TEXT;
769 boffset INT DEFAULT 0;
770 aoffset INT DEFAULT 0;
771 blimit INT DEFAULT 0;
772 alimit INT DEFAULT 0;
775 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q,thesauruses);
778 blimit := pagesize / 2;
781 IF pagesize % 2 <> 0 THEN
782 alimit := alimit + 1;
788 boffset := pagesize / 2;
791 IF pagesize % 2 <> 0 THEN
792 boffset := boffset + 1;
798 -- "bottom" half of the browse results
802 FROM authority.simple_heading ash
803 WHERE ash.atag = ANY (atag_list)
806 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
808 AND ash.sort_value < pivot_sort_value
809 ORDER BY ash.sort_value DESC
811 OFFSET ABS(page) * pagesize - boffset
812 ) x ORDER BY row_number DESC;
817 -- "bottom" half of the browse results
819 FROM authority.simple_heading ash
820 WHERE ash.atag = ANY (atag_list)
823 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
825 AND ash.sort_value >= pivot_sort_value
826 ORDER BY ash.sort_value
828 OFFSET ABS(page) * pagesize - aoffset;
831 $$ LANGUAGE PLPGSQL ROWS 10;
833 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
834 SELECT ARRAY_AGG(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
838 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
839 SELECT ARRAY_AGG(y) from (
840 SELECT unnest(ARRAY_CAT(
842 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
844 FROM authority.browse_axis_authority_field_map a
849 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
850 SELECT ARRAY_AGG(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
854 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
855 SELECT ARRAY_AGG(y) from (
856 SELECT unnest(ARRAY_CAT(
857 ARRAY[a.authority_field],
858 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
860 FROM authority.control_set_bib_field a
865 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
866 SELECT ARRAY_AGG(id) FROM authority.control_set_authority_field WHERE tag = $1
869 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
870 SELECT ARRAY_AGG(y) from (
871 SELECT unnest(ARRAY_CAT(
873 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
875 FROM authority.control_set_authority_field a
880 CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
881 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4, $5)
882 $$ LANGUAGE SQL ROWS 10;
884 CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
885 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4, $5)
886 $$ LANGUAGE SQL ROWS 10;
888 CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
889 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4, $5)
890 $$ LANGUAGE SQL ROWS 10;
892 CREATE OR REPLACE FUNCTION authority.axis_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
893 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
894 $$ LANGUAGE SQL ROWS 10;
896 CREATE OR REPLACE FUNCTION authority.btag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
897 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
898 $$ LANGUAGE SQL ROWS 10;
900 CREATE OR REPLACE FUNCTION authority.atag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
901 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
902 $$ LANGUAGE SQL ROWS 10;
905 CREATE OR REPLACE FUNCTION authority.simple_heading_browse_top( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
907 pivot_sort_value TEXT;
910 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q,thesauruses);
914 -- "bottom" half of the browse results
918 FROM authority.simple_heading ash
919 WHERE ash.atag = ANY (atag_list)
922 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
924 AND ash.sort_value < pivot_sort_value
925 ORDER BY ash.sort_value DESC
927 OFFSET (ABS(page) - 1) * pagesize
928 ) x ORDER BY row_number DESC;
933 -- "bottom" half of the browse results
935 FROM authority.simple_heading ash
936 WHERE ash.atag = ANY (atag_list)
939 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
941 AND ash.sort_value >= pivot_sort_value
942 ORDER BY ash.sort_value
944 OFFSET ABS(page) * pagesize ;
947 $$ LANGUAGE PLPGSQL ROWS 10;
949 CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
950 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4, $5)
951 $$ LANGUAGE SQL ROWS 10;
953 CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
954 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4, $5)
955 $$ LANGUAGE SQL ROWS 10;
957 CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
958 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4, $5)
959 $$ LANGUAGE SQL ROWS 10;
961 CREATE OR REPLACE FUNCTION authority.axis_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
962 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
963 $$ LANGUAGE SQL ROWS 10;
965 CREATE OR REPLACE FUNCTION authority.btag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
966 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
967 $$ LANGUAGE SQL ROWS 10;
969 CREATE OR REPLACE FUNCTION authority.atag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
970 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
971 $$ LANGUAGE SQL ROWS 10;
974 CREATE OR REPLACE FUNCTION authority.simple_heading_search_rank( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
976 FROM authority.simple_heading ash,
977 public.naco_normalize($2) t(term),
978 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
979 WHERE ash.atag = ANY ($1)
980 AND ash.index_vector @@ ptsq.term
983 ELSE ash.thesaurus = ANY(regexp_split_to_array($5, ','))
985 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
986 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
987 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
990 $$ LANGUAGE SQL ROWS 10;
992 CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
993 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4, $5)
994 $$ LANGUAGE SQL ROWS 10;
996 CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
997 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4, $5)
998 $$ LANGUAGE SQL ROWS 10;
1000 CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
1001 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4, $5)
1002 $$ LANGUAGE SQL ROWS 10;
1004 CREATE OR REPLACE FUNCTION authority.axis_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
1005 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
1006 $$ LANGUAGE SQL ROWS 10;
1008 CREATE OR REPLACE FUNCTION authority.btag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
1009 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
1010 $$ LANGUAGE SQL ROWS 10;
1012 CREATE OR REPLACE FUNCTION authority.atag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
1013 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
1014 $$ LANGUAGE SQL ROWS 10;
1017 CREATE OR REPLACE FUNCTION authority.simple_heading_search_heading( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
1019 FROM authority.simple_heading ash,
1020 public.naco_normalize($2) t(term),
1021 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
1022 WHERE ash.atag = ANY ($1)
1023 AND ash.index_vector @@ ptsq.term
1026 ELSE ash.thesaurus = ANY(regexp_split_to_array($5, ','))
1028 ORDER BY ash.sort_value
1031 $$ LANGUAGE SQL ROWS 10;
1033 CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
1034 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4, $5)
1035 $$ LANGUAGE SQL ROWS 10;
1037 CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
1038 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4, $5)
1039 $$ LANGUAGE SQL ROWS 10;
1041 CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
1042 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4, $5)
1043 $$ LANGUAGE SQL ROWS 10;
1045 CREATE OR REPLACE FUNCTION authority.axis_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
1046 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
1047 $$ LANGUAGE SQL ROWS 10;
1049 CREATE OR REPLACE FUNCTION authority.btag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
1050 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
1051 $$ LANGUAGE SQL ROWS 10;
1053 CREATE OR REPLACE FUNCTION authority.atag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
1054 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
1055 $$ LANGUAGE SQL ROWS 10;
1057 CREATE OR REPLACE FUNCTION authority.extract_headings(marc TEXT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
1059 idx authority.heading_field%ROWTYPE;
1060 xfrm config.xml_transform%ROWTYPE;
1062 transformed_xml TEXT;
1064 heading_node_list TEXT[];
1065 component_node TEXT;
1066 component_node_list TEXT[];
1068 normalized_text TEXT;
1073 base_thesaurus TEXT := NULL;
1074 output_row authority.heading;
1077 -- Loop over the indexing entries
1078 FOR idx IN SELECT * FROM authority.heading_field WHERE restrict IS NULL OR id = ANY (restrict) ORDER BY format LOOP
1080 output_row.field := idx.id;
1081 output_row.type := idx.heading_type;
1082 output_row.purpose := idx.heading_purpose;
1084 joiner := COALESCE(idx.joiner, ' ');
1086 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
1088 -- See if we can skip the XSLT ... it's expensive
1089 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1090 -- Can't skip the transform
1091 IF xfrm.xslt <> '---' THEN
1092 transformed_xml := oils_xslt_process(marc, xfrm.xslt);
1094 transformed_xml := marc;
1097 prev_xfrm := xfrm.name;
1100 IF idx.thesaurus_xpath IS NOT NULL THEN
1101 base_thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1104 heading_node_list := oils_xpath( idx.heading_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1106 FOR heading_node IN SELECT x FROM unnest(heading_node_list) AS x LOOP
1108 CONTINUE WHEN heading_node !~ E'^\\s*<';
1110 output_row.variant_type := NULL;
1111 output_row.related_type := NULL;
1112 output_row.thesaurus := NULL;
1113 output_row.heading := NULL;
1115 IF idx.heading_purpose = 'variant' AND idx.type_xpath IS NOT NULL THEN
1116 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1118 output_row.variant_type := type_value;
1119 EXCEPTION WHEN invalid_text_representation THEN
1120 RAISE NOTICE 'Do not recognize variant heading type %', type_value;
1123 IF idx.heading_purpose = 'related' AND idx.type_xpath IS NOT NULL THEN
1124 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1126 output_row.related_type := type_value;
1127 EXCEPTION WHEN invalid_text_representation THEN
1128 RAISE NOTICE 'Do not recognize related heading type %', type_value;
1132 IF idx.thesaurus_override_xpath IS NOT NULL THEN
1133 output_row.thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_override_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1135 IF output_row.thesaurus IS NULL THEN
1136 output_row.thesaurus := base_thesaurus;
1141 -- now iterate over components of heading
1142 component_node_list := oils_xpath( idx.component_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1143 FOR component_node IN SELECT x FROM unnest(component_node_list) AS x LOOP
1144 -- XXX much of this should be moved into oils_xpath_string...
1145 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
1146 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
1147 REGEXP_REPLACE( component_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
1148 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
1152 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
1154 IF raw_text IS NOT NULL THEN
1155 raw_text := raw_text || joiner;
1158 raw_text := COALESCE(raw_text,'') || curr_text;
1161 IF raw_text IS NOT NULL THEN
1162 output_row.heading := raw_text;
1163 normalized_text := raw_text;
1166 SELECT n.func AS func,
1167 n.param_count AS param_count,
1169 FROM config.index_normalizer n
1170 JOIN authority.heading_field_norm_map m ON (m.norm = n.id)
1171 WHERE m.field = idx.id
1174 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1175 quote_literal( normalized_text ) ||
1177 WHEN normalizer.param_count > 0
1178 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1181 ')' INTO normalized_text;
1185 output_row.normalized_heading := normalized_text;
1187 RETURN NEXT output_row;
1193 $func$ LANGUAGE PLPGSQL;
1195 CREATE OR REPLACE FUNCTION authority.extract_headings(rid BIGINT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
1197 auth authority.record_entry%ROWTYPE;
1198 output_row authority.heading;
1201 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
1203 RETURN QUERY SELECT * FROM authority.extract_headings(auth.marc, restrict);
1205 $func$ LANGUAGE PLPGSQL;