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
42 CREATE TABLE authority.control_set_bib_field (
43 id SERIAL PRIMARY KEY,
44 authority_field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
48 CREATE TABLE authority.thesaurus (
49 code TEXT PRIMARY KEY, -- MARC21 thesaurus code
50 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
51 name TEXT NOT NULL UNIQUE, -- i18n
52 description TEXT -- i18n
55 CREATE TABLE authority.browse_axis (
56 code TEXT PRIMARY KEY,
57 name TEXT UNIQUE NOT NULL, -- i18n
58 sorter TEXT REFERENCES config.record_attr_definition (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
62 CREATE TABLE authority.browse_axis_authority_field_map (
63 id SERIAL PRIMARY KEY,
64 axis TEXT NOT NULL REFERENCES authority.browse_axis (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
65 field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
68 CREATE TABLE authority.record_entry (
69 id BIGSERIAL PRIMARY KEY,
70 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
71 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
72 creator INT NOT NULL DEFAULT 1,
73 editor INT NOT NULL DEFAULT 1,
74 active BOOL NOT NULL DEFAULT TRUE,
75 deleted BOOL NOT NULL DEFAULT FALSE,
77 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
79 last_xact_id TEXT NOT NULL,
82 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
83 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
84 CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
85 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();
86 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
87 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
89 CREATE TABLE authority.bib_linking (
90 id BIGSERIAL PRIMARY KEY,
91 bib BIGINT NOT NULL REFERENCES biblio.record_entry (id),
92 authority BIGINT NOT NULL REFERENCES authority.record_entry (id)
94 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
95 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
97 CREATE TABLE authority.record_note (
98 id BIGSERIAL PRIMARY KEY,
99 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
101 creator INT NOT NULL DEFAULT 1,
102 editor INT NOT NULL DEFAULT 1,
103 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
104 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
106 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
107 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
108 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
110 CREATE TABLE authority.rec_descriptor (
111 id BIGSERIAL PRIMARY KEY,
117 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
119 CREATE TABLE authority.full_rec (
120 id BIGSERIAL PRIMARY KEY,
121 record BIGINT NOT NULL,
122 tag CHAR(3) NOT NULL,
127 index_vector tsvector NOT NULL
129 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
130 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
131 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
132 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
133 CREATE TRIGGER authority_full_rec_fti_trigger
134 BEFORE UPDATE OR INSERT ON authority.full_rec
135 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
137 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
138 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
139 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
140 /* But we still need this (boooo) for paging using >, <, etc */
141 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
143 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);
145 -- Intended to be used in a unique index on authority.record_entry like so:
146 -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
147 -- ON authority.record_entry (authority.normalize_heading(marc))
148 -- WHERE deleted IS FALSE or deleted = FALSE;
149 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
151 acsaf authority.control_set_authority_field%ROWTYPE;
160 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT;
162 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
165 SELECT control_set INTO cset
166 FROM authority.control_set_authority_field
167 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
171 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
172 IF thes_code IS NULL THEN
174 ELSIF thes_code = 'z' THEN
175 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
179 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
180 tag_used := acsaf.tag;
181 nfi_used := acsaf.nfi;
183 FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
184 tmp_text := oils_xpath_string('//*[@tag="'||tag_used||'"]/*[@code="'||sf||'"]', marcxml);
186 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
188 tmp_text := SUBSTRING(
193 oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml),
208 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
209 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
212 EXIT WHEN heading_text <> '';
215 IF heading_text <> '' THEN
216 IF no_thesaurus IS TRUE THEN
217 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
219 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
222 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
227 $func$ LANGUAGE PLPGSQL IMMUTABLE;
229 CREATE TABLE authority.simple_heading (
230 id BIGSERIAL PRIMARY KEY,
231 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
232 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
234 sort_value TEXT NOT NULL,
235 index_vector tsvector NOT NULL
237 CREATE TRIGGER authority_simple_heading_fti_trigger
238 BEFORE UPDATE OR INSERT ON authority.simple_heading
239 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
241 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector);
242 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
243 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
245 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
247 res authority.simple_heading%ROWTYPE;
248 acsaf authority.control_set_authority_field%ROWTYPE;
258 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT;
261 res.record := auth_id;
263 SELECT control_set INTO cset
264 FROM authority.control_set_authority_field
265 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]) )
268 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
270 res.atag := acsaf.id;
271 tag_used := acsaf.tag;
272 nfi_used := acsaf.nfi;
274 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
277 FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
278 heading_text := heading_text || COALESCE( ' ' || oils_xpath_string('//*[@code="'||sf||'"]',tmp_xml::TEXT), '');
281 heading_text := public.naco_normalize(heading_text);
283 IF nfi_used IS NOT NULL THEN
285 sort_text := SUBSTRING(
290 oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml),
302 sort_text := heading_text;
305 IF heading_text IS NOT NULL AND heading_text <> '' THEN
306 res.value := heading_text;
307 res.sort_value := sort_text;
317 $func$ LANGUAGE PLPGSQL IMMUTABLE;
319 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
320 SELECT authority.normalize_heading($1, TRUE);
321 $func$ LANGUAGE SQL IMMUTABLE;
323 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
324 SELECT authority.normalize_heading($1, FALSE);
325 $func$ LANGUAGE SQL IMMUTABLE;
327 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
328 Extract the authority heading, thesaurus, and NACO-normalized values
329 from an authority record. The primary purpose is to build a unique
330 index to defend against duplicated authority records from the same
334 -- Adding indexes using oils_xpath_string() for the main entry tags described in
335 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
336 -- the existing index on authority.normalize_heading() helps already with a record in hand
337 CREATE OR REPLACE VIEW authority.tracing_links AS
338 SELECT main.record AS record,
340 main.tag AS main_tag,
341 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
342 substr(link.value,1,1) AS relationship,
343 substr(link.value,2,1) AS use_restriction,
344 substr(link.value,3,1) AS deprecation,
345 substr(link.value,4,1) AS display_restriction,
347 link.tag AS link_tag,
348 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
349 authority.normalize_heading(are.marc) AS normalized_main_value
350 FROM authority.full_rec main
351 JOIN authority.record_entry are ON (main.record = are.id)
352 JOIN authority.control_set_authority_field main_entry
353 ON (main_entry.tag = main.tag
354 AND main_entry.main_entry IS NULL
355 AND main.subfield = 'a' )
356 JOIN authority.control_set_authority_field sub_entry
357 ON (main_entry.id = sub_entry.main_entry)
358 JOIN authority.full_rec link
359 ON (link.record = main.record
360 AND link.tag = sub_entry.tag
361 AND link.subfield = 'w' );
363 -- Function to generate an ephemeral overlay template from an authority record
364 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
367 main_entry authority.control_set_authority_field%ROWTYPE;
368 bib_field authority.control_set_bib_field%ROWTYPE;
369 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
370 replace_data XML[] DEFAULT '{}'::XML[];
371 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
374 IF auth_id IS NULL THEN
378 -- Default to the LoC controll set
379 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
381 -- if none, make a best guess
383 SELECT control_set INTO cset
384 FROM authority.control_set_authority_field
386 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
387 FROM authority.record_entry
393 -- if STILL none, no-op change
397 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
398 XMLELEMENT( name leader, '00881nam a2200193 4500'),
401 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
404 XMLATTRIBUTES('d' AS code),
411 FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP
412 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
413 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
414 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
415 replace_data := replace_data || XMLELEMENT( name datafield, XMLATTRIBUTES(bib_field.tag AS tag), XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]);
416 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
424 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
425 XMLELEMENT( name leader, '00881nam a2200193 4500'),
429 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
432 XMLATTRIBUTES('r' AS code),
433 ARRAY_TO_STRING(replace_rules,',')
438 $f$ STABLE LANGUAGE PLPGSQL;
440 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
441 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
444 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
446 moved_objects INT := 0;
448 bib_rec biblio.record_entry%ROWTYPE;
449 auth_link authority.bib_linking%ROWTYPE;
453 -- Defining our terms:
454 -- "target record" = the record that will survive the merge
455 -- "source record" = the record that is sacrifing its existence and being
456 -- replaced by the target record
458 -- 1. Update all bib records with the ID from target_record in their $0
461 FROM biblio.record_entry bre
462 JOIN authority.bib_linking abl ON abl.bib = bre.id
463 WHERE abl.authority = source_record
466 UPDATE biblio.record_entry
467 SET marc = REGEXP_REPLACE(
469 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
470 E'\\1' || target_record || '<',
473 WHERE id = bib_rec.id;
475 moved_objects := moved_objects + 1;
478 -- 2. Grab the current value of reingest on same MARC flag
479 SELECT enabled INTO ingest_same
480 FROM config.internal_flag
481 WHERE name = 'ingest.reingest.force_on_same_marc'
484 -- 3. Temporarily set reingest on same to TRUE
485 UPDATE config.internal_flag
487 WHERE name = 'ingest.reingest.force_on_same_marc'
490 -- 4. Make a harmless update to target_record to trigger auto-update
491 -- in linked bibliographic records
492 UPDATE authority.record_entry
494 WHERE id = target_record;
496 -- 5. "Delete" source_record
497 DELETE FROM authority.record_entry WHERE id = source_record;
499 -- 6. Set "reingest on same MARC" flag back to initial value
500 UPDATE config.internal_flag
501 SET enabled = ingest_same
502 WHERE name = 'ingest.reingest.force_on_same_marc'
505 RETURN moved_objects;
507 $func$ LANGUAGE plpgsql;
510 -- Support function used to find the pivot for alpha-heading-browse style searching
511 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$
513 sort_value_row RECORD;
518 t_term := public.naco_normalize(q);
520 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
521 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
524 FROM authority.simple_heading ash
525 WHERE ash.atag = ANY (a)
526 AND ash.sort_value >= t_term
527 ORDER BY rank DESC, ash.sort_value
530 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
531 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
534 FROM authority.simple_heading ash
535 WHERE ash.atag = ANY (a)
536 AND ash.value >= t_term
537 ORDER BY rank DESC, ash.sort_value
540 IF value_row.rank > sort_value_row.rank THEN
541 RETURN value_row.sort_value;
543 RETURN sort_value_row.sort_value;
548 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 $$
550 pivot_sort_value TEXT;
551 boffset INT DEFAULT 0;
552 aoffset INT DEFAULT 0;
553 blimit INT DEFAULT 0;
554 alimit INT DEFAULT 0;
557 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
560 blimit := pagesize / 2;
563 IF pagesize % 2 <> 0 THEN
564 alimit := alimit + 1;
570 boffset := pagesize / 2;
573 IF pagesize % 2 <> 0 THEN
574 boffset := boffset + 1;
580 -- "bottom" half of the browse results
584 FROM authority.simple_heading ash
585 WHERE ash.atag = ANY (atag_list)
586 AND ash.sort_value < pivot_sort_value
587 ORDER BY ash.sort_value DESC
589 OFFSET ABS(page) * pagesize - boffset
590 ) x ORDER BY row_number DESC;
595 -- "bottom" half of the browse results
597 FROM authority.simple_heading ash
598 WHERE ash.atag = ANY (atag_list)
599 AND ash.sort_value >= pivot_sort_value
600 ORDER BY ash.sort_value
602 OFFSET ABS(page) * pagesize - aoffset;
605 $$ LANGUAGE PLPGSQL ROWS 10;
607 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
608 SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
611 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
614 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
616 FROM authority.browse_axis_authority_field_map a
622 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
623 SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
626 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
628 ARRAY[a.authority_field],
629 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
631 FROM authority.control_set_bib_field a
637 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
638 SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1
641 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
644 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
646 FROM authority.control_set_authority_field a
652 CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
653 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4)
654 $$ LANGUAGE SQL ROWS 10;
656 CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
657 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4)
658 $$ LANGUAGE SQL ROWS 10;
660 CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
661 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4)
662 $$ LANGUAGE SQL ROWS 10;
664 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 $$
665 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4)
666 $$ LANGUAGE SQL ROWS 10;
668 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 $$
669 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4)
670 $$ LANGUAGE SQL ROWS 10;
672 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 $$
673 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4)
674 $$ LANGUAGE SQL ROWS 10;
677 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 $$
679 pivot_sort_value TEXT;
682 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
686 -- "bottom" half of the browse results
690 FROM authority.simple_heading ash
691 WHERE ash.atag = ANY (atag_list)
692 AND ash.sort_value < pivot_sort_value
693 ORDER BY ash.sort_value DESC
695 OFFSET (ABS(page) - 1) * pagesize
696 ) x ORDER BY row_number DESC;
701 -- "bottom" half of the browse results
703 FROM authority.simple_heading ash
704 WHERE ash.atag = ANY (atag_list)
705 AND ash.sort_value >= pivot_sort_value
706 ORDER BY ash.sort_value
708 OFFSET ABS(page) * pagesize ;
711 $$ LANGUAGE PLPGSQL ROWS 10;
713 CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
714 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4)
715 $$ LANGUAGE SQL ROWS 10;
717 CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
718 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4)
719 $$ LANGUAGE SQL ROWS 10;
721 CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
722 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4)
723 $$ LANGUAGE SQL ROWS 10;
725 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 $$
726 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4)
727 $$ LANGUAGE SQL ROWS 10;
729 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 $$
730 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4)
731 $$ LANGUAGE SQL ROWS 10;
733 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 $$
734 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4)
735 $$ LANGUAGE SQL ROWS 10;
738 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 $$
740 FROM authority.simple_heading ash,
741 public.naco_normalize($2) t(term),
742 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
743 WHERE ash.atag = ANY ($1)
744 AND ash.index_vector @@ ptsq.term
745 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
746 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
747 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
750 $$ LANGUAGE SQL ROWS 10;
752 CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
753 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4)
754 $$ LANGUAGE SQL ROWS 10;
756 CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
757 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4)
758 $$ LANGUAGE SQL ROWS 10;
760 CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
761 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4)
762 $$ LANGUAGE SQL ROWS 10;
764 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 $$
765 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4)
766 $$ LANGUAGE SQL ROWS 10;
768 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 $$
769 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4)
770 $$ LANGUAGE SQL ROWS 10;
772 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 $$
773 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4)
774 $$ LANGUAGE SQL ROWS 10;
777 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 $$
779 FROM authority.simple_heading ash,
780 public.naco_normalize($2) t(term),
781 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
782 WHERE ash.atag = ANY ($1)
783 AND ash.index_vector @@ ptsq.term
784 ORDER BY ash.sort_value
787 $$ LANGUAGE SQL ROWS 10;
789 CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
790 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4)
791 $$ LANGUAGE SQL ROWS 10;
793 CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
794 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4)
795 $$ LANGUAGE SQL ROWS 10;
797 CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
798 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4)
799 $$ LANGUAGE SQL ROWS 10;
801 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 $$
802 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4)
803 $$ LANGUAGE SQL ROWS 10;
805 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 $$
806 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4)
807 $$ LANGUAGE SQL ROWS 10;
809 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 $$
810 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4)
811 $$ LANGUAGE SQL ROWS 10;