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 (SUBSTRING(value FOR 1024) 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 (SUBSTRING(value FOR 1024));
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))
345 FOR sf_node IN SELECT unnest(oils_xpath('//*[local-name() = "subfield" and contains("'||acsaf.sf_list||'",@code)]',tag_node))
348 tmp_text := oils_xpath_string('.', sf_node);
349 sf := oils_xpath_string('//*/@code', sf_node);
351 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
353 tmp_text := SUBSTRING(
358 oils_xpath_string('//*[local-name() = "datafield"]/@ind'||nfi_used, tag_node),
373 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
374 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
378 EXIT WHEN heading_text <> '';
381 EXIT WHEN heading_text <> '';
384 IF heading_text <> '' THEN
385 IF no_thesaurus IS TRUE THEN
386 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
388 thes_code := authority.extract_thesaurus(marcxml);
389 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
392 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
397 $func$ LANGUAGE PLPGSQL STABLE STRICT;
399 CREATE TABLE authority.simple_heading (
400 id BIGSERIAL PRIMARY KEY,
401 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
402 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
404 sort_value TEXT NOT NULL,
405 index_vector tsvector NOT NULL,
408 CREATE TRIGGER authority_simple_heading_fti_trigger
409 BEFORE UPDATE OR INSERT ON authority.simple_heading
410 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
412 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIN (index_vector);
413 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
414 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
415 CREATE INDEX authority_simple_heading_record_idx ON authority.simple_heading (record);
416 CREATE INDEX authority_simple_heading_thesaurus_idx ON authority.simple_heading (thesaurus);
418 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
420 res authority.simple_heading%ROWTYPE;
421 acsaf authority.control_set_authority_field%ROWTYPE;
422 heading_row authority.heading%ROWTYPE;
433 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
436 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
439 SELECT control_set INTO cset
440 FROM authority.control_set_authority_field
441 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
445 res.record := auth_id;
446 res.thesaurus := authority.extract_thesaurus(marcxml);
448 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
449 res.atag := acsaf.id;
451 IF acsaf.heading_field IS NULL THEN
452 tag_used := acsaf.tag;
453 nfi_used := acsaf.nfi;
454 joiner_text := COALESCE(acsaf.joiner, ' ');
456 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)::TEXT[]) LOOP
458 heading_text := COALESCE(
459 oils_xpath_string('//*[local-name()="subfield" and contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml, joiner_text),
463 IF nfi_used IS NOT NULL THEN
465 sort_text := SUBSTRING(
470 oils_xpath_string('//*[local-name()="datafield"]/@ind'||nfi_used, tmp_xml::TEXT),
482 sort_text := heading_text;
485 IF heading_text IS NOT NULL AND heading_text <> '' THEN
486 res.value := heading_text;
487 res.sort_value := public.naco_normalize(sort_text);
488 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
494 FOR heading_row IN SELECT * FROM authority.extract_headings(marcxml, ARRAY[acsaf.heading_field]) LOOP
495 res.value := heading_row.heading;
496 res.sort_value := heading_row.normalized_heading;
497 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
505 $func$ LANGUAGE PLPGSQL STABLE STRICT;
507 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
508 SELECT authority.normalize_heading($1, TRUE);
509 $func$ LANGUAGE SQL STABLE STRICT;
511 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
512 SELECT authority.normalize_heading($1, FALSE);
513 $func$ LANGUAGE SQL STABLE STRICT;
515 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
516 Extract the authority heading, thesaurus, and NACO-normalized values
517 from an authority record. The primary purpose is to build a unique
518 index to defend against duplicated authority records from the same
522 -- Store these in line with the MARC for easier indexing
523 CREATE OR REPLACE FUNCTION authority.normalize_heading_for_upsert () RETURNS TRIGGER AS $f$
525 NEW.heading := authority.normalize_heading( NEW.marc );
526 NEW.simple_heading := authority.simple_normalize_heading( NEW.marc );
529 $f$ LANGUAGE PLPGSQL;
531 CREATE TRIGGER update_headings_tgr BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE authority.normalize_heading_for_upsert();
533 -- Adding indexes using oils_xpath_string() for the main entry tags described in
534 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
535 -- the existing index on authority.normalize_heading() helps already with a record in hand
536 CREATE OR REPLACE VIEW authority.tracing_links AS
537 SELECT main.record AS record,
539 main.tag AS main_tag,
540 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
541 substr(link.value,1,1) AS relationship,
542 substr(link.value,2,1) AS use_restriction,
543 substr(link.value,3,1) AS deprecation,
544 substr(link.value,4,1) AS display_restriction,
546 link.tag AS link_tag,
547 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
548 are.heading AS normalized_main_value
549 FROM authority.full_rec main
550 JOIN authority.record_entry are ON (main.record = are.id)
551 JOIN authority.control_set_authority_field main_entry
552 ON (main_entry.tag = main.tag
553 AND main_entry.main_entry IS NULL
554 AND main.subfield = 'a' )
555 JOIN authority.control_set_authority_field sub_entry
556 ON (main_entry.id = sub_entry.main_entry)
557 JOIN authority.full_rec link
558 ON (link.record = main.record
559 AND link.tag = sub_entry.tag
560 AND link.subfield = 'w' );
562 -- Function to generate an ephemeral overlay template from an authority record
563 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
566 main_entry authority.control_set_authority_field%ROWTYPE;
567 bib_field authority.control_set_bib_field%ROWTYPE;
568 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
570 replace_data XML[] DEFAULT '{}'::XML[];
571 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
576 IF auth_id IS NULL THEN
580 -- Default to the LoC controll set
581 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
583 -- if none, make a best guess
585 SELECT control_set INTO cset
586 FROM authority.control_set_authority_field
588 SELECT UNNEST(XPATH('//*[local-name()="datafield" and starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
589 FROM authority.record_entry
595 -- if STILL none, no-op change
599 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
600 XMLELEMENT( name leader, '00881nam a2200193 4500'),
603 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
606 XMLATTRIBUTES('d' AS code),
613 FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP
614 auth_field := XPATH('//*[local-name()="datafield" and @tag="'||main_entry.tag||'"][1]',source_xml::XML);
615 auth_i1 := (XPATH('//*[local-name()="datafield"]/@ind1',auth_field[1]))[1];
616 auth_i2 := (XPATH('//*[local-name()="datafield"]/@ind2',auth_field[1]))[1];
617 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
618 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
619 SELECT XMLELEMENT( -- XMLAGG avoids magical <element> creation, but requires unnest subquery
621 XMLATTRIBUTES(bib_field.tag AS tag, auth_i1 AS ind1, auth_i2 AS ind2),
623 ) INTO tmp_data FROM UNNEST(XPATH('//*[local-name()="subfield"]', auth_field[1]));
624 replace_data := replace_data || tmp_data;
625 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
632 SELECT XMLAGG(UNNEST) INTO tmp_data FROM UNNEST(replace_data);
636 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
637 XMLELEMENT( name leader, '00881nam a2200193 4500'),
641 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
644 XMLATTRIBUTES('r' AS code),
645 ARRAY_TO_STRING(replace_rules,',')
650 $f$ STABLE LANGUAGE PLPGSQL;
652 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
653 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
656 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
658 moved_objects INT := 0;
660 bib_rec biblio.record_entry%ROWTYPE;
661 auth_link authority.bib_linking%ROWTYPE;
665 -- Defining our terms:
666 -- "target record" = the record that will survive the merge
667 -- "source record" = the record that is sacrifing its existence and being
668 -- replaced by the target record
670 -- 1. Update all bib records with the ID from target_record in their $0
673 FROM biblio.record_entry bre
674 JOIN authority.bib_linking abl ON abl.bib = bre.id
675 WHERE abl.authority = source_record
678 UPDATE biblio.record_entry
679 SET marc = REGEXP_REPLACE(
681 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
682 E'\\1' || target_record || '<',
685 WHERE id = bib_rec.id;
687 moved_objects := moved_objects + 1;
690 -- 2. Grab the current value of reingest on same MARC flag
691 SELECT enabled INTO ingest_same
692 FROM config.internal_flag
693 WHERE name = 'ingest.reingest.force_on_same_marc'
696 -- 3. Temporarily set reingest on same to TRUE
697 UPDATE config.internal_flag
699 WHERE name = 'ingest.reingest.force_on_same_marc'
702 -- 4. Make a harmless update to target_record to trigger auto-update
703 -- in linked bibliographic records
704 UPDATE authority.record_entry
706 WHERE id = target_record;
708 -- 5. "Delete" source_record
709 DELETE FROM authority.record_entry WHERE id = source_record;
711 -- 6. Set "reingest on same MARC" flag back to initial value
712 UPDATE config.internal_flag
713 SET enabled = ingest_same
714 WHERE name = 'ingest.reingest.force_on_same_marc'
717 RETURN moved_objects;
719 $func$ LANGUAGE plpgsql;
722 -- Support function used to find the pivot for alpha-heading-browse style searching
723 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT, thesauruses TEXT DEFAULT '' ) RETURNS TEXT AS $$
725 sort_value_row RECORD;
730 t_term := public.naco_normalize(q);
732 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
733 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
736 FROM authority.simple_heading ash
737 WHERE ash.atag = ANY (a)
738 AND ash.sort_value >= t_term
741 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
743 ORDER BY rank DESC, ash.sort_value
746 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
747 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
750 FROM authority.simple_heading ash
751 WHERE ash.atag = ANY (a)
752 AND ash.value >= t_term
755 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
757 ORDER BY rank DESC, ash.sort_value
760 IF value_row.rank > sort_value_row.rank THEN
761 RETURN value_row.sort_value;
763 RETURN sort_value_row.sort_value;
768 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 $$
770 pivot_sort_value TEXT;
771 boffset INT DEFAULT 0;
772 aoffset INT DEFAULT 0;
773 blimit INT DEFAULT 0;
774 alimit INT DEFAULT 0;
777 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q,thesauruses);
780 blimit := pagesize / 2;
783 IF pagesize % 2 <> 0 THEN
784 alimit := alimit + 1;
790 boffset := pagesize / 2;
793 IF pagesize % 2 <> 0 THEN
794 boffset := boffset + 1;
800 -- "bottom" half of the browse results
804 FROM authority.simple_heading ash
805 WHERE ash.atag = ANY (atag_list)
808 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
810 AND ash.sort_value < pivot_sort_value
811 ORDER BY ash.sort_value DESC
813 OFFSET ABS(page) * pagesize - boffset
814 ) x ORDER BY row_number DESC;
819 -- "bottom" half of the browse results
821 FROM authority.simple_heading ash
822 WHERE ash.atag = ANY (atag_list)
825 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
827 AND ash.sort_value >= pivot_sort_value
828 ORDER BY ash.sort_value
830 OFFSET ABS(page) * pagesize - aoffset;
833 $$ LANGUAGE PLPGSQL ROWS 10;
835 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
836 SELECT ARRAY_AGG(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
840 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
841 SELECT ARRAY_AGG(y) from (
842 SELECT unnest(ARRAY_CAT(
844 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
846 FROM authority.browse_axis_authority_field_map a
851 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
852 SELECT ARRAY_AGG(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
856 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
857 SELECT ARRAY_AGG(y) from (
858 SELECT unnest(ARRAY_CAT(
859 ARRAY[a.authority_field],
860 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
862 FROM authority.control_set_bib_field a
867 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
868 SELECT ARRAY_AGG(id) FROM authority.control_set_authority_field WHERE tag = $1
871 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
872 SELECT ARRAY_AGG(y) from (
873 SELECT unnest(ARRAY_CAT(
875 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
877 FROM authority.control_set_authority_field a
882 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 $$
883 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4, $5)
884 $$ LANGUAGE SQL ROWS 10;
886 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 $$
887 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4, $5)
888 $$ LANGUAGE SQL ROWS 10;
890 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 $$
891 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4, $5)
892 $$ LANGUAGE SQL ROWS 10;
894 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 $$
895 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
896 $$ LANGUAGE SQL ROWS 10;
898 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 $$
899 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
900 $$ LANGUAGE SQL ROWS 10;
902 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 $$
903 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
904 $$ LANGUAGE SQL ROWS 10;
907 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 $$
909 pivot_sort_value TEXT;
912 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q,thesauruses);
916 -- "bottom" half of the browse results
920 FROM authority.simple_heading ash
921 WHERE ash.atag = ANY (atag_list)
924 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
926 AND ash.sort_value < pivot_sort_value
927 ORDER BY ash.sort_value DESC
929 OFFSET (ABS(page) - 1) * pagesize
930 ) x ORDER BY row_number DESC;
935 -- "bottom" half of the browse results
937 FROM authority.simple_heading ash
938 WHERE ash.atag = ANY (atag_list)
941 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
943 AND ash.sort_value >= pivot_sort_value
944 ORDER BY ash.sort_value
946 OFFSET ABS(page) * pagesize ;
949 $$ LANGUAGE PLPGSQL ROWS 10;
951 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 $$
952 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4, $5)
953 $$ LANGUAGE SQL ROWS 10;
955 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 $$
956 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4, $5)
957 $$ LANGUAGE SQL ROWS 10;
959 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 $$
960 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4, $5)
961 $$ LANGUAGE SQL ROWS 10;
963 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 $$
964 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
965 $$ LANGUAGE SQL ROWS 10;
967 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 $$
968 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
969 $$ LANGUAGE SQL ROWS 10;
971 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 $$
972 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
973 $$ LANGUAGE SQL ROWS 10;
976 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 $$
978 FROM authority.simple_heading ash,
979 public.naco_normalize($2) t(term),
980 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
981 WHERE ash.atag = ANY ($1)
982 AND ash.index_vector @@ ptsq.term
985 ELSE ash.thesaurus = ANY(regexp_split_to_array($5, ','))
987 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
988 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
989 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
992 $$ LANGUAGE SQL ROWS 10;
994 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 $$
995 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4, $5)
996 $$ LANGUAGE SQL ROWS 10;
998 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 $$
999 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4, $5)
1000 $$ LANGUAGE SQL ROWS 10;
1002 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 $$
1003 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4, $5)
1004 $$ LANGUAGE SQL ROWS 10;
1006 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 $$
1007 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
1008 $$ LANGUAGE SQL ROWS 10;
1010 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 $$
1011 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
1012 $$ LANGUAGE SQL ROWS 10;
1014 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 $$
1015 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
1016 $$ LANGUAGE SQL ROWS 10;
1019 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 $$
1021 FROM authority.simple_heading ash,
1022 public.naco_normalize($2) t(term),
1023 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
1024 WHERE ash.atag = ANY ($1)
1025 AND ash.index_vector @@ ptsq.term
1028 ELSE ash.thesaurus = ANY(regexp_split_to_array($5, ','))
1030 ORDER BY ash.sort_value
1033 $$ LANGUAGE SQL ROWS 10;
1035 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 $$
1036 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4, $5)
1037 $$ LANGUAGE SQL ROWS 10;
1039 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 $$
1040 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4, $5)
1041 $$ LANGUAGE SQL ROWS 10;
1043 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 $$
1044 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4, $5)
1045 $$ LANGUAGE SQL ROWS 10;
1047 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 $$
1048 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
1049 $$ LANGUAGE SQL ROWS 10;
1051 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 $$
1052 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
1053 $$ LANGUAGE SQL ROWS 10;
1055 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 $$
1056 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
1057 $$ LANGUAGE SQL ROWS 10;
1059 CREATE OR REPLACE FUNCTION authority.extract_headings(marc TEXT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
1061 idx authority.heading_field%ROWTYPE;
1062 xfrm config.xml_transform%ROWTYPE;
1064 transformed_xml TEXT;
1066 heading_node_list TEXT[];
1067 component_node TEXT;
1068 component_node_list TEXT[];
1070 normalized_text TEXT;
1075 base_thesaurus TEXT := NULL;
1076 output_row authority.heading;
1079 -- Loop over the indexing entries
1080 FOR idx IN SELECT * FROM authority.heading_field WHERE restrict IS NULL OR id = ANY (restrict) ORDER BY format LOOP
1082 output_row.field := idx.id;
1083 output_row.type := idx.heading_type;
1084 output_row.purpose := idx.heading_purpose;
1086 joiner := COALESCE(idx.joiner, ' ');
1088 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
1090 -- See if we can skip the XSLT ... it's expensive
1091 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1092 -- Can't skip the transform
1093 IF xfrm.xslt <> '---' THEN
1094 transformed_xml := oils_xslt_process(marc, xfrm.xslt);
1096 transformed_xml := marc;
1099 prev_xfrm := xfrm.name;
1102 IF idx.thesaurus_xpath IS NOT NULL THEN
1103 base_thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1106 heading_node_list := oils_xpath( idx.heading_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1108 FOR heading_node IN SELECT x FROM unnest(heading_node_list) AS x LOOP
1110 CONTINUE WHEN heading_node !~ E'^\\s*<';
1112 output_row.variant_type := NULL;
1113 output_row.related_type := NULL;
1114 output_row.thesaurus := NULL;
1115 output_row.heading := NULL;
1117 IF idx.heading_purpose = 'variant' AND idx.type_xpath IS NOT NULL THEN
1118 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1120 output_row.variant_type := type_value;
1121 EXCEPTION WHEN invalid_text_representation THEN
1122 RAISE NOTICE 'Do not recognize variant heading type %', type_value;
1125 IF idx.heading_purpose = 'related' AND idx.type_xpath IS NOT NULL THEN
1126 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1128 output_row.related_type := type_value;
1129 EXCEPTION WHEN invalid_text_representation THEN
1130 RAISE NOTICE 'Do not recognize related heading type %', type_value;
1134 IF idx.thesaurus_override_xpath IS NOT NULL THEN
1135 output_row.thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_override_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1137 IF output_row.thesaurus IS NULL THEN
1138 output_row.thesaurus := base_thesaurus;
1143 -- now iterate over components of heading
1144 component_node_list := oils_xpath( idx.component_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1145 FOR component_node IN SELECT x FROM unnest(component_node_list) AS x LOOP
1146 -- XXX much of this should be moved into oils_xpath_string...
1147 curr_text := ARRAY_TO_STRING(array_remove(array_remove(
1148 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
1149 REGEXP_REPLACE( component_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
1150 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
1154 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
1156 IF raw_text IS NOT NULL THEN
1157 raw_text := raw_text || joiner;
1160 raw_text := COALESCE(raw_text,'') || curr_text;
1163 IF raw_text IS NOT NULL THEN
1164 output_row.heading := raw_text;
1165 normalized_text := raw_text;
1168 SELECT n.func AS func,
1169 n.param_count AS param_count,
1171 FROM config.index_normalizer n
1172 JOIN authority.heading_field_norm_map m ON (m.norm = n.id)
1173 WHERE m.field = idx.id
1176 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1177 quote_literal( normalized_text ) ||
1179 WHEN normalizer.param_count > 0
1180 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1183 ')' INTO normalized_text;
1187 output_row.normalized_heading := normalized_text;
1189 RETURN NEXT output_row;
1195 $func$ LANGUAGE PLPGSQL;
1197 CREATE OR REPLACE FUNCTION authority.extract_headings(rid BIGINT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
1199 auth authority.record_entry%ROWTYPE;
1200 output_row authority.heading;
1203 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
1205 RETURN QUERY SELECT * FROM authority.extract_headings(auth.marc, restrict);
1207 $func$ LANGUAGE PLPGSQL;