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 name TEXT NOT NULL, -- i18n
39 description TEXT, -- i18n
40 linking_subfield CHAR(1)
43 CREATE TABLE authority.control_set_bib_field (
44 id SERIAL PRIMARY KEY,
45 authority_field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
49 CREATE TABLE authority.thesaurus (
50 code TEXT PRIMARY KEY, -- MARC21 thesaurus code
51 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
52 name TEXT NOT NULL UNIQUE, -- i18n
53 description TEXT -- i18n
56 CREATE TABLE authority.browse_axis (
57 code TEXT PRIMARY KEY,
58 name TEXT UNIQUE NOT NULL, -- i18n
59 sorter TEXT REFERENCES config.record_attr_definition (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
63 CREATE TABLE authority.browse_axis_authority_field_map (
64 id SERIAL PRIMARY KEY,
65 axis TEXT NOT NULL REFERENCES authority.browse_axis (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
66 field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
69 CREATE TABLE authority.record_entry (
70 id BIGSERIAL PRIMARY KEY,
71 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
72 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
73 creator INT NOT NULL DEFAULT 1,
74 editor INT NOT NULL DEFAULT 1,
75 active BOOL NOT NULL DEFAULT TRUE,
76 deleted BOOL NOT NULL DEFAULT FALSE,
78 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
80 last_xact_id TEXT NOT NULL,
83 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
84 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
85 CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
86 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();
87 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
88 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
90 CREATE TABLE authority.authority_linking (
91 id BIGSERIAL PRIMARY KEY,
92 source BIGINT REFERENCES authority.record_entry (id) NOT NULL,
93 target BIGINT REFERENCES authority.record_entry (id) NOT NULL,
94 field INT REFERENCES authority.control_set_authority_field (id) NOT NULL
97 CREATE TABLE authority.bib_linking (
98 id BIGSERIAL PRIMARY KEY,
99 bib BIGINT NOT NULL REFERENCES biblio.record_entry (id),
100 authority BIGINT NOT NULL REFERENCES authority.record_entry (id)
102 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
103 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
105 CREATE TABLE authority.record_note (
106 id BIGSERIAL PRIMARY KEY,
107 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
109 creator INT NOT NULL DEFAULT 1,
110 editor INT NOT NULL DEFAULT 1,
111 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
112 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
114 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
115 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
116 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
118 CREATE TABLE authority.rec_descriptor (
119 id BIGSERIAL PRIMARY KEY,
125 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
127 CREATE TABLE authority.full_rec (
128 id BIGSERIAL PRIMARY KEY,
129 record BIGINT NOT NULL,
130 tag CHAR(3) NOT NULL,
135 index_vector tsvector NOT NULL
137 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
138 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
139 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
140 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
141 CREATE TRIGGER authority_full_rec_fti_trigger
142 BEFORE UPDATE OR INSERT ON authority.full_rec
143 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
145 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
146 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
147 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
148 /* But we still need this (boooo) for paging using >, <, etc */
149 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
151 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);
153 -- Intended to be used in a unique index on authority.record_entry like so:
154 -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
155 -- ON authority.record_entry (authority.normalize_heading(marc))
156 -- WHERE deleted IS FALSE or deleted = FALSE;
157 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
159 acsaf authority.control_set_authority_field%ROWTYPE;
170 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
172 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
175 SELECT control_set INTO cset
176 FROM authority.control_set_authority_field
177 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
181 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
182 IF thes_code IS NULL THEN
184 ELSIF thes_code = 'z' THEN
185 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
189 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
190 tag_used := acsaf.tag;
191 nfi_used := acsaf.nfi;
194 FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
195 FOR sf_node IN SELECT unnest(oils_xpath('//*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
197 tmp_text := oils_xpath_string('.', sf_node);
198 sf := oils_xpath_string('./@code', sf_node);
200 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
202 tmp_text := SUBSTRING(
207 oils_xpath_string('./@ind'||nfi_used, tag_node),
222 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
223 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
227 EXIT WHEN heading_text <> '';
230 EXIT WHEN heading_text <> '';
233 IF heading_text <> '' THEN
234 IF no_thesaurus IS TRUE THEN
235 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
237 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
240 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
245 $func$ LANGUAGE PLPGSQL IMMUTABLE;
247 CREATE TABLE authority.simple_heading (
248 id BIGSERIAL PRIMARY KEY,
249 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
250 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
252 sort_value TEXT NOT NULL,
253 index_vector tsvector NOT NULL
255 CREATE TRIGGER authority_simple_heading_fti_trigger
256 BEFORE UPDATE OR INSERT ON authority.simple_heading
257 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
259 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector);
260 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
261 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
263 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
265 res authority.simple_heading%ROWTYPE;
266 acsaf authority.control_set_authority_field%ROWTYPE;
276 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT;
279 res.record := auth_id;
281 -- XXX this SELECT control_set... business below should actually only
282 -- be a fallback. We should (SELECT control_set FROM authority.record_entry
283 -- WHERE id = auth_id) when we have an auth_id, and use that if we can get
286 SELECT control_set INTO cset
287 FROM authority.control_set_authority_field
288 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]) )
291 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
293 res.atag := acsaf.id;
294 tag_used := acsaf.tag;
295 nfi_used := acsaf.nfi;
297 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
299 heading_text := public.naco_normalize(
301 oils_xpath_string('//*[contains("'||acsaf.sf_list||'",@code)]',tmp_xml::TEXT, ' '),
306 IF nfi_used IS NOT NULL THEN
308 sort_text := SUBSTRING(
313 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
325 sort_text := heading_text;
328 IF heading_text IS NOT NULL AND heading_text <> '' THEN
329 res.value := heading_text;
330 res.sort_value := sort_text;
340 $func$ LANGUAGE PLPGSQL IMMUTABLE;
342 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
343 SELECT authority.normalize_heading($1, TRUE);
344 $func$ LANGUAGE SQL IMMUTABLE;
346 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
347 SELECT authority.normalize_heading($1, FALSE);
348 $func$ LANGUAGE SQL IMMUTABLE;
350 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
351 Extract the authority heading, thesaurus, and NACO-normalized values
352 from an authority record. The primary purpose is to build a unique
353 index to defend against duplicated authority records from the same
357 -- Adding indexes using oils_xpath_string() for the main entry tags described in
358 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
359 -- the existing index on authority.normalize_heading() helps already with a record in hand
360 CREATE OR REPLACE VIEW authority.tracing_links AS
361 SELECT main.record AS record,
363 main.tag AS main_tag,
364 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
365 substr(link.value,1,1) AS relationship,
366 substr(link.value,2,1) AS use_restriction,
367 substr(link.value,3,1) AS deprecation,
368 substr(link.value,4,1) AS display_restriction,
370 link.tag AS link_tag,
371 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
372 authority.normalize_heading(are.marc) AS normalized_main_value
373 FROM authority.full_rec main
374 JOIN authority.record_entry are ON (main.record = are.id)
375 JOIN authority.control_set_authority_field main_entry
376 ON (main_entry.tag = main.tag
377 AND main_entry.main_entry IS NULL
378 AND main.subfield = 'a' )
379 JOIN authority.control_set_authority_field sub_entry
380 ON (main_entry.id = sub_entry.main_entry)
381 JOIN authority.full_rec link
382 ON (link.record = main.record
383 AND link.tag = sub_entry.tag
384 AND link.subfield = 'w' );
386 -- Function to generate an ephemeral overlay template from an authority record
387 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
390 main_entry authority.control_set_authority_field%ROWTYPE;
391 bib_field authority.control_set_bib_field%ROWTYPE;
392 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
393 replace_data XML[] DEFAULT '{}'::XML[];
394 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
397 IF auth_id IS NULL THEN
401 -- Default to the LoC controll set
402 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
404 -- if none, make a best guess
406 SELECT control_set INTO cset
407 FROM authority.control_set_authority_field
409 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
410 FROM authority.record_entry
416 -- if STILL none, no-op change
420 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
421 XMLELEMENT( name leader, '00881nam a2200193 4500'),
424 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
427 XMLATTRIBUTES('d' AS code),
434 FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP
435 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
436 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
437 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
438 replace_data := replace_data || XMLELEMENT( name datafield, XMLATTRIBUTES(bib_field.tag AS tag), XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]);
439 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
447 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
448 XMLELEMENT( name leader, '00881nam a2200193 4500'),
452 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
455 XMLATTRIBUTES('r' AS code),
456 ARRAY_TO_STRING(replace_rules,',')
461 $f$ STABLE LANGUAGE PLPGSQL;
463 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
464 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
467 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
469 moved_objects INT := 0;
471 bib_rec biblio.record_entry%ROWTYPE;
472 auth_link authority.bib_linking%ROWTYPE;
476 -- Defining our terms:
477 -- "target record" = the record that will survive the merge
478 -- "source record" = the record that is sacrifing its existence and being
479 -- replaced by the target record
481 -- 1. Update all bib records with the ID from target_record in their $0
484 FROM biblio.record_entry bre
485 JOIN authority.bib_linking abl ON abl.bib = bre.id
486 WHERE abl.authority = source_record
489 UPDATE biblio.record_entry
490 SET marc = REGEXP_REPLACE(
492 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
493 E'\\1' || target_record || '<',
496 WHERE id = bib_rec.id;
498 moved_objects := moved_objects + 1;
501 -- 2. Grab the current value of reingest on same MARC flag
502 SELECT enabled INTO ingest_same
503 FROM config.internal_flag
504 WHERE name = 'ingest.reingest.force_on_same_marc'
507 -- 3. Temporarily set reingest on same to TRUE
508 UPDATE config.internal_flag
510 WHERE name = 'ingest.reingest.force_on_same_marc'
513 -- 4. Make a harmless update to target_record to trigger auto-update
514 -- in linked bibliographic records
515 UPDATE authority.record_entry
517 WHERE id = target_record;
519 -- 5. "Delete" source_record
520 DELETE FROM authority.record_entry WHERE id = source_record;
522 -- 6. Set "reingest on same MARC" flag back to initial value
523 UPDATE config.internal_flag
524 SET enabled = ingest_same
525 WHERE name = 'ingest.reingest.force_on_same_marc'
528 RETURN moved_objects;
530 $func$ LANGUAGE plpgsql;
533 -- Support function used to find the pivot for alpha-heading-browse style searching
534 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$
536 sort_value_row RECORD;
541 t_term := public.naco_normalize(q);
543 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
544 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
547 FROM authority.simple_heading ash
548 WHERE ash.atag = ANY (a)
549 AND ash.sort_value >= t_term
550 ORDER BY rank DESC, ash.sort_value
553 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
554 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
557 FROM authority.simple_heading ash
558 WHERE ash.atag = ANY (a)
559 AND ash.value >= t_term
560 ORDER BY rank DESC, ash.sort_value
563 IF value_row.rank > sort_value_row.rank THEN
564 RETURN value_row.sort_value;
566 RETURN sort_value_row.sort_value;
571 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 $$
573 pivot_sort_value TEXT;
574 boffset INT DEFAULT 0;
575 aoffset INT DEFAULT 0;
576 blimit INT DEFAULT 0;
577 alimit INT DEFAULT 0;
580 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
583 blimit := pagesize / 2;
586 IF pagesize % 2 <> 0 THEN
587 alimit := alimit + 1;
593 boffset := pagesize / 2;
596 IF pagesize % 2 <> 0 THEN
597 boffset := boffset + 1;
603 -- "bottom" half of the browse results
607 FROM authority.simple_heading ash
608 WHERE ash.atag = ANY (atag_list)
609 AND ash.sort_value < pivot_sort_value
610 ORDER BY ash.sort_value DESC
612 OFFSET ABS(page) * pagesize - boffset
613 ) x ORDER BY row_number DESC;
618 -- "bottom" half of the browse results
620 FROM authority.simple_heading ash
621 WHERE ash.atag = ANY (atag_list)
622 AND ash.sort_value >= pivot_sort_value
623 ORDER BY ash.sort_value
625 OFFSET ABS(page) * pagesize - aoffset;
628 $$ LANGUAGE PLPGSQL ROWS 10;
630 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
631 SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
635 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
636 SELECT ARRAY_AGG(y) from (
637 SELECT unnest(ARRAY_CAT(
639 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
641 FROM authority.browse_axis_authority_field_map a
646 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
647 SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
651 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
652 SELECT ARRAY_AGG(y) from (
653 SELECT unnest(ARRAY_CAT(
654 ARRAY[a.authority_field],
655 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
657 FROM authority.control_set_bib_field a
662 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
663 SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1
666 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
667 SELECT ARRAY_AGG(y) from (
668 SELECT unnest(ARRAY_CAT(
670 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
672 FROM authority.control_set_authority_field a
677 CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
678 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4)
679 $$ LANGUAGE SQL ROWS 10;
681 CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
682 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4)
683 $$ LANGUAGE SQL ROWS 10;
685 CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
686 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4)
687 $$ LANGUAGE SQL ROWS 10;
689 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 $$
690 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4)
691 $$ LANGUAGE SQL ROWS 10;
693 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 $$
694 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4)
695 $$ LANGUAGE SQL ROWS 10;
697 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 $$
698 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4)
699 $$ LANGUAGE SQL ROWS 10;
702 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 $$
704 pivot_sort_value TEXT;
707 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
711 -- "bottom" half of the browse results
715 FROM authority.simple_heading ash
716 WHERE ash.atag = ANY (atag_list)
717 AND ash.sort_value < pivot_sort_value
718 ORDER BY ash.sort_value DESC
720 OFFSET (ABS(page) - 1) * pagesize
721 ) x ORDER BY row_number DESC;
726 -- "bottom" half of the browse results
728 FROM authority.simple_heading ash
729 WHERE ash.atag = ANY (atag_list)
730 AND ash.sort_value >= pivot_sort_value
731 ORDER BY ash.sort_value
733 OFFSET ABS(page) * pagesize ;
736 $$ LANGUAGE PLPGSQL ROWS 10;
738 CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
739 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4)
740 $$ LANGUAGE SQL ROWS 10;
742 CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
743 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4)
744 $$ LANGUAGE SQL ROWS 10;
746 CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
747 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4)
748 $$ LANGUAGE SQL ROWS 10;
750 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 $$
751 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4)
752 $$ LANGUAGE SQL ROWS 10;
754 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 $$
755 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4)
756 $$ LANGUAGE SQL ROWS 10;
758 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 $$
759 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4)
760 $$ LANGUAGE SQL ROWS 10;
763 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 $$
765 FROM authority.simple_heading ash,
766 public.naco_normalize($2) t(term),
767 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
768 WHERE ash.atag = ANY ($1)
769 AND ash.index_vector @@ ptsq.term
770 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
771 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
772 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
775 $$ LANGUAGE SQL ROWS 10;
777 CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
778 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4)
779 $$ LANGUAGE SQL ROWS 10;
781 CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
782 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4)
783 $$ LANGUAGE SQL ROWS 10;
785 CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
786 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4)
787 $$ LANGUAGE SQL ROWS 10;
789 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 $$
790 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4)
791 $$ LANGUAGE SQL ROWS 10;
793 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 $$
794 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4)
795 $$ LANGUAGE SQL ROWS 10;
797 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 $$
798 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4)
799 $$ LANGUAGE SQL ROWS 10;
802 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 $$
804 FROM authority.simple_heading ash,
805 public.naco_normalize($2) t(term),
806 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
807 WHERE ash.atag = ANY ($1)
808 AND ash.index_vector @@ ptsq.term
809 ORDER BY ash.sort_value
812 $$ LANGUAGE SQL ROWS 10;
814 CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
815 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4)
816 $$ LANGUAGE SQL ROWS 10;
818 CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
819 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4)
820 $$ LANGUAGE SQL ROWS 10;
822 CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
823 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4)
824 $$ LANGUAGE SQL ROWS 10;
826 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 $$
827 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4)
828 $$ LANGUAGE SQL ROWS 10;
830 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 $$
831 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4)
832 $$ LANGUAGE SQL ROWS 10;
834 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 $$
835 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4)
836 $$ LANGUAGE SQL ROWS 10;