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 CREATE TABLE authority.control_set (
26 id SERIAL PRIMARY KEY,
27 name TEXT NOT NULL UNIQUE, -- i18n
28 description TEXT -- i18n
31 CREATE TABLE authority.control_set_authority_field (
32 id SERIAL PRIMARY KEY,
33 main_entry INT REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
34 control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
36 nfi CHAR(1), -- non-filing indicator
37 sf_list TEXT NOT NULL,
38 display_sf_list TEXT NOT NULL,
39 name TEXT NOT NULL, -- i18n
40 description TEXT, -- i18n
41 linking_subfield CHAR(1)
44 CREATE TABLE authority.control_set_bib_field (
45 id SERIAL PRIMARY KEY,
46 authority_field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
50 CREATE TABLE authority.thesaurus (
51 code TEXT PRIMARY KEY, -- MARC21 thesaurus code
52 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
53 name TEXT NOT NULL UNIQUE, -- i18n
54 description TEXT -- i18n
57 CREATE TABLE authority.browse_axis (
58 code TEXT PRIMARY KEY,
59 name TEXT UNIQUE NOT NULL, -- i18n
60 sorter TEXT REFERENCES config.record_attr_definition (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
64 CREATE TABLE authority.browse_axis_authority_field_map (
65 id SERIAL PRIMARY KEY,
66 axis TEXT NOT NULL REFERENCES authority.browse_axis (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
67 field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
70 CREATE TABLE authority.record_entry (
71 id BIGSERIAL PRIMARY KEY,
72 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
73 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
74 creator INT NOT NULL DEFAULT 1,
75 editor INT NOT NULL DEFAULT 1,
76 active BOOL NOT NULL DEFAULT TRUE,
77 deleted BOOL NOT NULL DEFAULT FALSE,
79 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
81 last_xact_id TEXT NOT NULL,
84 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
85 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
86 CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
87 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();
88 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
89 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
91 CREATE TABLE authority.authority_linking (
92 id BIGSERIAL PRIMARY KEY,
93 source BIGINT REFERENCES authority.record_entry (id) NOT NULL,
94 target BIGINT REFERENCES authority.record_entry (id) NOT NULL,
95 field INT REFERENCES authority.control_set_authority_field (id) NOT NULL
98 CREATE TABLE authority.bib_linking (
99 id BIGSERIAL PRIMARY KEY,
100 bib BIGINT NOT NULL REFERENCES biblio.record_entry (id),
101 authority BIGINT NOT NULL REFERENCES authority.record_entry (id)
103 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
104 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
106 CREATE TABLE authority.record_note (
107 id BIGSERIAL PRIMARY KEY,
108 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
110 creator INT NOT NULL DEFAULT 1,
111 editor INT NOT NULL DEFAULT 1,
112 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
113 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
115 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
116 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
117 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
119 CREATE TABLE authority.rec_descriptor (
120 id BIGSERIAL PRIMARY KEY,
126 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
128 CREATE TABLE authority.full_rec (
129 id BIGSERIAL PRIMARY KEY,
130 record BIGINT NOT NULL,
131 tag CHAR(3) NOT NULL,
136 index_vector tsvector NOT NULL
138 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
139 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
140 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
141 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
142 CREATE TRIGGER authority_full_rec_fti_trigger
143 BEFORE UPDATE OR INSERT ON authority.full_rec
144 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
146 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
147 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
148 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
149 /* But we still need this (boooo) for paging using >, <, etc */
150 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
152 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);
154 -- Intended to be used in a unique index on authority.record_entry like so:
155 -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
156 -- ON authority.record_entry (authority.normalize_heading(marc))
157 -- WHERE deleted IS FALSE or deleted = FALSE;
158 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
160 acsaf authority.control_set_authority_field%ROWTYPE;
171 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
173 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
176 SELECT control_set INTO cset
177 FROM authority.control_set_authority_field
178 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
182 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
183 IF thes_code IS NULL THEN
185 ELSIF thes_code = 'z' THEN
186 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
190 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
191 tag_used := acsaf.tag;
192 nfi_used := acsaf.nfi;
195 FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
196 FOR sf_node IN SELECT unnest(oils_xpath('//*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
198 tmp_text := oils_xpath_string('.', sf_node);
199 sf := oils_xpath_string('./@code', sf_node);
201 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
203 tmp_text := SUBSTRING(
208 oils_xpath_string('./@ind'||nfi_used, tag_node),
223 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
224 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
228 EXIT WHEN heading_text <> '';
231 EXIT WHEN heading_text <> '';
234 IF heading_text <> '' THEN
235 IF no_thesaurus IS TRUE THEN
236 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
238 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
241 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
246 $func$ LANGUAGE PLPGSQL IMMUTABLE;
248 CREATE TABLE authority.simple_heading (
249 id BIGSERIAL PRIMARY KEY,
250 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
251 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
253 sort_value TEXT NOT NULL,
254 index_vector tsvector NOT NULL
256 CREATE TRIGGER authority_simple_heading_fti_trigger
257 BEFORE UPDATE OR INSERT ON authority.simple_heading
258 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
260 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector);
261 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
262 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
264 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
266 res authority.simple_heading%ROWTYPE;
267 acsaf authority.control_set_authority_field%ROWTYPE;
277 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT;
280 res.record := auth_id;
282 -- XXX this SELECT control_set... business below should actually only
283 -- be a fallback. We should (SELECT control_set FROM authority.record_entry
284 -- WHERE id = auth_id) when we have an auth_id, and use that if we can get
287 SELECT control_set INTO cset
288 FROM authority.control_set_authority_field
289 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]) )
292 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
294 res.atag := acsaf.id;
295 tag_used := acsaf.tag;
296 nfi_used := acsaf.nfi;
298 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
300 heading_text := public.naco_normalize(
302 oils_xpath_string('//*[contains("'||acsaf.sf_list||'",@code)]',tmp_xml::TEXT, ' '),
307 IF nfi_used IS NOT NULL THEN
309 sort_text := SUBSTRING(
314 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
326 sort_text := heading_text;
329 IF heading_text IS NOT NULL AND heading_text <> '' THEN
330 res.value := heading_text;
331 res.sort_value := sort_text;
341 $func$ LANGUAGE PLPGSQL IMMUTABLE;
343 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
344 SELECT authority.normalize_heading($1, TRUE);
345 $func$ LANGUAGE SQL IMMUTABLE;
347 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
348 SELECT authority.normalize_heading($1, FALSE);
349 $func$ LANGUAGE SQL IMMUTABLE;
351 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
352 Extract the authority heading, thesaurus, and NACO-normalized values
353 from an authority record. The primary purpose is to build a unique
354 index to defend against duplicated authority records from the same
358 -- Adding indexes using oils_xpath_string() for the main entry tags described in
359 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
360 -- the existing index on authority.normalize_heading() helps already with a record in hand
361 CREATE OR REPLACE VIEW authority.tracing_links AS
362 SELECT main.record AS record,
364 main.tag AS main_tag,
365 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
366 substr(link.value,1,1) AS relationship,
367 substr(link.value,2,1) AS use_restriction,
368 substr(link.value,3,1) AS deprecation,
369 substr(link.value,4,1) AS display_restriction,
371 link.tag AS link_tag,
372 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
373 authority.normalize_heading(are.marc) AS normalized_main_value
374 FROM authority.full_rec main
375 JOIN authority.record_entry are ON (main.record = are.id)
376 JOIN authority.control_set_authority_field main_entry
377 ON (main_entry.tag = main.tag
378 AND main_entry.main_entry IS NULL
379 AND main.subfield = 'a' )
380 JOIN authority.control_set_authority_field sub_entry
381 ON (main_entry.id = sub_entry.main_entry)
382 JOIN authority.full_rec link
383 ON (link.record = main.record
384 AND link.tag = sub_entry.tag
385 AND link.subfield = 'w' );
387 -- Function to generate an ephemeral overlay template from an authority record
388 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
391 main_entry authority.control_set_authority_field%ROWTYPE;
392 bib_field authority.control_set_bib_field%ROWTYPE;
393 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
394 replace_data XML[] DEFAULT '{}'::XML[];
395 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
398 IF auth_id IS NULL THEN
402 -- Default to the LoC controll set
403 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
405 -- if none, make a best guess
407 SELECT control_set INTO cset
408 FROM authority.control_set_authority_field
410 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
411 FROM authority.record_entry
417 -- if STILL none, no-op change
421 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
422 XMLELEMENT( name leader, '00881nam a2200193 4500'),
425 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
428 XMLATTRIBUTES('d' AS code),
435 FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP
436 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
437 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
438 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
439 replace_data := replace_data || XMLELEMENT( name datafield, XMLATTRIBUTES(bib_field.tag AS tag), XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]);
440 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
448 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
449 XMLELEMENT( name leader, '00881nam a2200193 4500'),
453 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
456 XMLATTRIBUTES('r' AS code),
457 ARRAY_TO_STRING(replace_rules,',')
462 $f$ STABLE LANGUAGE PLPGSQL;
464 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
465 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
468 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
470 moved_objects INT := 0;
472 bib_rec biblio.record_entry%ROWTYPE;
473 auth_link authority.bib_linking%ROWTYPE;
477 -- Defining our terms:
478 -- "target record" = the record that will survive the merge
479 -- "source record" = the record that is sacrifing its existence and being
480 -- replaced by the target record
482 -- 1. Update all bib records with the ID from target_record in their $0
485 FROM biblio.record_entry bre
486 JOIN authority.bib_linking abl ON abl.bib = bre.id
487 WHERE abl.authority = source_record
490 UPDATE biblio.record_entry
491 SET marc = REGEXP_REPLACE(
493 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
494 E'\\1' || target_record || '<',
497 WHERE id = bib_rec.id;
499 moved_objects := moved_objects + 1;
502 -- 2. Grab the current value of reingest on same MARC flag
503 SELECT enabled INTO ingest_same
504 FROM config.internal_flag
505 WHERE name = 'ingest.reingest.force_on_same_marc'
508 -- 3. Temporarily set reingest on same to TRUE
509 UPDATE config.internal_flag
511 WHERE name = 'ingest.reingest.force_on_same_marc'
514 -- 4. Make a harmless update to target_record to trigger auto-update
515 -- in linked bibliographic records
516 UPDATE authority.record_entry
518 WHERE id = target_record;
520 -- 5. "Delete" source_record
521 DELETE FROM authority.record_entry WHERE id = source_record;
523 -- 6. Set "reingest on same MARC" flag back to initial value
524 UPDATE config.internal_flag
525 SET enabled = ingest_same
526 WHERE name = 'ingest.reingest.force_on_same_marc'
529 RETURN moved_objects;
531 $func$ LANGUAGE plpgsql;
534 -- Support function used to find the pivot for alpha-heading-browse style searching
535 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$
537 sort_value_row RECORD;
542 t_term := public.naco_normalize(q);
544 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
545 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
548 FROM authority.simple_heading ash
549 WHERE ash.atag = ANY (a)
550 AND ash.sort_value >= t_term
551 ORDER BY rank DESC, ash.sort_value
554 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
555 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
558 FROM authority.simple_heading ash
559 WHERE ash.atag = ANY (a)
560 AND ash.value >= t_term
561 ORDER BY rank DESC, ash.sort_value
564 IF value_row.rank > sort_value_row.rank THEN
565 RETURN value_row.sort_value;
567 RETURN sort_value_row.sort_value;
572 CREATE OR REPLACE FUNCTION authority.simple_heading_browse_center( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
574 pivot_sort_value TEXT;
575 boffset INT DEFAULT 0;
576 aoffset INT DEFAULT 0;
577 blimit INT DEFAULT 0;
578 alimit INT DEFAULT 0;
581 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
584 blimit := pagesize / 2;
587 IF pagesize % 2 <> 0 THEN
588 alimit := alimit + 1;
594 boffset := pagesize / 2;
597 IF pagesize % 2 <> 0 THEN
598 boffset := boffset + 1;
604 -- "bottom" half of the browse results
608 FROM authority.simple_heading ash
609 WHERE ash.atag = ANY (atag_list)
610 AND ash.sort_value < pivot_sort_value
611 ORDER BY ash.sort_value DESC
613 OFFSET ABS(page) * pagesize - boffset
614 ) x ORDER BY row_number DESC;
619 -- "bottom" half of the browse results
621 FROM authority.simple_heading ash
622 WHERE ash.atag = ANY (atag_list)
623 AND ash.sort_value >= pivot_sort_value
624 ORDER BY ash.sort_value
626 OFFSET ABS(page) * pagesize - aoffset;
629 $$ LANGUAGE PLPGSQL ROWS 10;
631 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
632 SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
636 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
637 SELECT ARRAY_AGG(y) from (
638 SELECT unnest(ARRAY_CAT(
640 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
642 FROM authority.browse_axis_authority_field_map a
647 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
648 SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
652 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
653 SELECT ARRAY_AGG(y) from (
654 SELECT unnest(ARRAY_CAT(
655 ARRAY[a.authority_field],
656 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
658 FROM authority.control_set_bib_field a
663 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
664 SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1
667 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
668 SELECT ARRAY_AGG(y) from (
669 SELECT unnest(ARRAY_CAT(
671 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
673 FROM authority.control_set_authority_field a
678 CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
679 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4)
680 $$ LANGUAGE SQL ROWS 10;
682 CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
683 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4)
684 $$ LANGUAGE SQL ROWS 10;
686 CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
687 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4)
688 $$ LANGUAGE SQL ROWS 10;
690 CREATE OR REPLACE FUNCTION authority.axis_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
691 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4)
692 $$ LANGUAGE SQL ROWS 10;
694 CREATE OR REPLACE FUNCTION authority.btag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
695 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4)
696 $$ LANGUAGE SQL ROWS 10;
698 CREATE OR REPLACE FUNCTION authority.atag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
699 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4)
700 $$ LANGUAGE SQL ROWS 10;
703 CREATE OR REPLACE FUNCTION authority.simple_heading_browse_top( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
705 pivot_sort_value TEXT;
708 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
712 -- "bottom" half of the browse results
716 FROM authority.simple_heading ash
717 WHERE ash.atag = ANY (atag_list)
718 AND ash.sort_value < pivot_sort_value
719 ORDER BY ash.sort_value DESC
721 OFFSET (ABS(page) - 1) * pagesize
722 ) x ORDER BY row_number DESC;
727 -- "bottom" half of the browse results
729 FROM authority.simple_heading ash
730 WHERE ash.atag = ANY (atag_list)
731 AND ash.sort_value >= pivot_sort_value
732 ORDER BY ash.sort_value
734 OFFSET ABS(page) * pagesize ;
737 $$ LANGUAGE PLPGSQL ROWS 10;
739 CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
740 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4)
741 $$ LANGUAGE SQL ROWS 10;
743 CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
744 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4)
745 $$ LANGUAGE SQL ROWS 10;
747 CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
748 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4)
749 $$ LANGUAGE SQL ROWS 10;
751 CREATE OR REPLACE FUNCTION authority.axis_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
752 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4)
753 $$ LANGUAGE SQL ROWS 10;
755 CREATE OR REPLACE FUNCTION authority.btag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
756 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4)
757 $$ LANGUAGE SQL ROWS 10;
759 CREATE OR REPLACE FUNCTION authority.atag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
760 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4)
761 $$ LANGUAGE SQL ROWS 10;
764 CREATE OR REPLACE FUNCTION authority.simple_heading_search_rank( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
766 FROM authority.simple_heading ash,
767 public.naco_normalize($2) t(term),
768 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
769 WHERE ash.atag = ANY ($1)
770 AND ash.index_vector @@ ptsq.term
771 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
772 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
773 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
776 $$ LANGUAGE SQL ROWS 10;
778 CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
779 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4)
780 $$ LANGUAGE SQL ROWS 10;
782 CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
783 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4)
784 $$ LANGUAGE SQL ROWS 10;
786 CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
787 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4)
788 $$ LANGUAGE SQL ROWS 10;
790 CREATE OR REPLACE FUNCTION authority.axis_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
791 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4)
792 $$ LANGUAGE SQL ROWS 10;
794 CREATE OR REPLACE FUNCTION authority.btag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
795 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4)
796 $$ LANGUAGE SQL ROWS 10;
798 CREATE OR REPLACE FUNCTION authority.atag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
799 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4)
800 $$ LANGUAGE SQL ROWS 10;
803 CREATE OR REPLACE FUNCTION authority.simple_heading_search_heading( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
805 FROM authority.simple_heading ash,
806 public.naco_normalize($2) t(term),
807 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
808 WHERE ash.atag = ANY ($1)
809 AND ash.index_vector @@ ptsq.term
810 ORDER BY ash.sort_value
813 $$ LANGUAGE SQL ROWS 10;
815 CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
816 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4)
817 $$ LANGUAGE SQL ROWS 10;
819 CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
820 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4)
821 $$ LANGUAGE SQL ROWS 10;
823 CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
824 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4)
825 $$ LANGUAGE SQL ROWS 10;
827 CREATE OR REPLACE FUNCTION authority.axis_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
828 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4)
829 $$ LANGUAGE SQL ROWS 10;
831 CREATE OR REPLACE FUNCTION authority.btag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
832 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4)
833 $$ LANGUAGE SQL ROWS 10;
835 CREATE OR REPLACE FUNCTION authority.atag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
836 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4)
837 $$ LANGUAGE SQL ROWS 10;