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;
162 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
164 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
167 SELECT control_set INTO cset
168 FROM authority.control_set_authority_field
169 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
173 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
174 IF thes_code IS NULL THEN
176 ELSIF thes_code = 'z' THEN
177 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
181 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
182 tag_used := acsaf.tag;
183 nfi_used := acsaf.nfi;
186 FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
187 FOR sf_node IN SELECT unnest(oils_xpath('//*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
189 tmp_text := oils_xpath_string('.', sf_node);
190 sf := oils_xpath_string('./@code', sf_node);
192 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
194 tmp_text := SUBSTRING(
199 oils_xpath_string('./@ind'||nfi_used, tag_node),
214 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
215 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
219 EXIT WHEN heading_text <> '';
222 EXIT WHEN heading_text <> '';
225 IF heading_text <> '' THEN
226 IF no_thesaurus IS TRUE THEN
227 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
229 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
232 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
237 $func$ LANGUAGE PLPGSQL IMMUTABLE;
239 CREATE TABLE authority.simple_heading (
240 id BIGSERIAL PRIMARY KEY,
241 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
242 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
244 sort_value TEXT NOT NULL,
245 index_vector tsvector NOT NULL
247 CREATE TRIGGER authority_simple_heading_fti_trigger
248 BEFORE UPDATE OR INSERT ON authority.simple_heading
249 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
251 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector);
252 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
253 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
255 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
257 res authority.simple_heading%ROWTYPE;
258 acsaf authority.control_set_authority_field%ROWTYPE;
268 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT;
271 res.record := auth_id;
273 SELECT control_set INTO cset
274 FROM authority.control_set_authority_field
275 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]) )
278 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
280 res.atag := acsaf.id;
281 tag_used := acsaf.tag;
282 nfi_used := acsaf.nfi;
284 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
286 heading_text := public.naco_normalize(
288 oils_xpath_string('//*[contains("'||acsaf.sf_list||'",@code)]',tmp_xml::TEXT, ' '),
293 IF nfi_used IS NOT NULL THEN
295 sort_text := SUBSTRING(
300 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
312 sort_text := heading_text;
315 IF heading_text IS NOT NULL AND heading_text <> '' THEN
316 res.value := heading_text;
317 res.sort_value := sort_text;
327 $func$ LANGUAGE PLPGSQL IMMUTABLE;
329 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
330 SELECT authority.normalize_heading($1, TRUE);
331 $func$ LANGUAGE SQL IMMUTABLE;
333 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
334 SELECT authority.normalize_heading($1, FALSE);
335 $func$ LANGUAGE SQL IMMUTABLE;
337 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
338 Extract the authority heading, thesaurus, and NACO-normalized values
339 from an authority record. The primary purpose is to build a unique
340 index to defend against duplicated authority records from the same
344 -- Adding indexes using oils_xpath_string() for the main entry tags described in
345 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
346 -- the existing index on authority.normalize_heading() helps already with a record in hand
347 CREATE OR REPLACE VIEW authority.tracing_links AS
348 SELECT main.record AS record,
350 main.tag AS main_tag,
351 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
352 substr(link.value,1,1) AS relationship,
353 substr(link.value,2,1) AS use_restriction,
354 substr(link.value,3,1) AS deprecation,
355 substr(link.value,4,1) AS display_restriction,
357 link.tag AS link_tag,
358 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
359 authority.normalize_heading(are.marc) AS normalized_main_value
360 FROM authority.full_rec main
361 JOIN authority.record_entry are ON (main.record = are.id)
362 JOIN authority.control_set_authority_field main_entry
363 ON (main_entry.tag = main.tag
364 AND main_entry.main_entry IS NULL
365 AND main.subfield = 'a' )
366 JOIN authority.control_set_authority_field sub_entry
367 ON (main_entry.id = sub_entry.main_entry)
368 JOIN authority.full_rec link
369 ON (link.record = main.record
370 AND link.tag = sub_entry.tag
371 AND link.subfield = 'w' );
373 -- Function to generate an ephemeral overlay template from an authority record
374 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
377 main_entry authority.control_set_authority_field%ROWTYPE;
378 bib_field authority.control_set_bib_field%ROWTYPE;
379 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
380 replace_data XML[] DEFAULT '{}'::XML[];
381 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
384 IF auth_id IS NULL THEN
388 -- Default to the LoC controll set
389 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
391 -- if none, make a best guess
393 SELECT control_set INTO cset
394 FROM authority.control_set_authority_field
396 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
397 FROM authority.record_entry
403 -- if STILL none, no-op change
407 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
408 XMLELEMENT( name leader, '00881nam a2200193 4500'),
411 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
414 XMLATTRIBUTES('d' AS code),
421 FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP
422 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
423 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
424 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
425 replace_data := replace_data || XMLELEMENT( name datafield, XMLATTRIBUTES(bib_field.tag AS tag), XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]);
426 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
434 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
435 XMLELEMENT( name leader, '00881nam a2200193 4500'),
439 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
442 XMLATTRIBUTES('r' AS code),
443 ARRAY_TO_STRING(replace_rules,',')
448 $f$ STABLE LANGUAGE PLPGSQL;
450 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
451 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
454 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
456 moved_objects INT := 0;
458 bib_rec biblio.record_entry%ROWTYPE;
459 auth_link authority.bib_linking%ROWTYPE;
463 -- Defining our terms:
464 -- "target record" = the record that will survive the merge
465 -- "source record" = the record that is sacrifing its existence and being
466 -- replaced by the target record
468 -- 1. Update all bib records with the ID from target_record in their $0
471 FROM biblio.record_entry bre
472 JOIN authority.bib_linking abl ON abl.bib = bre.id
473 WHERE abl.authority = source_record
476 UPDATE biblio.record_entry
477 SET marc = REGEXP_REPLACE(
479 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
480 E'\\1' || target_record || '<',
483 WHERE id = bib_rec.id;
485 moved_objects := moved_objects + 1;
488 -- 2. Grab the current value of reingest on same MARC flag
489 SELECT enabled INTO ingest_same
490 FROM config.internal_flag
491 WHERE name = 'ingest.reingest.force_on_same_marc'
494 -- 3. Temporarily set reingest on same to TRUE
495 UPDATE config.internal_flag
497 WHERE name = 'ingest.reingest.force_on_same_marc'
500 -- 4. Make a harmless update to target_record to trigger auto-update
501 -- in linked bibliographic records
502 UPDATE authority.record_entry
504 WHERE id = target_record;
506 -- 5. "Delete" source_record
507 DELETE FROM authority.record_entry WHERE id = source_record;
509 -- 6. Set "reingest on same MARC" flag back to initial value
510 UPDATE config.internal_flag
511 SET enabled = ingest_same
512 WHERE name = 'ingest.reingest.force_on_same_marc'
515 RETURN moved_objects;
517 $func$ LANGUAGE plpgsql;
520 -- Support function used to find the pivot for alpha-heading-browse style searching
521 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$
523 sort_value_row RECORD;
528 t_term := public.naco_normalize(q);
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.sort_value >= t_term
537 ORDER BY rank DESC, ash.sort_value
540 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
541 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
544 FROM authority.simple_heading ash
545 WHERE ash.atag = ANY (a)
546 AND ash.value >= t_term
547 ORDER BY rank DESC, ash.sort_value
550 IF value_row.rank > sort_value_row.rank THEN
551 RETURN value_row.sort_value;
553 RETURN sort_value_row.sort_value;
558 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 $$
560 pivot_sort_value TEXT;
561 boffset INT DEFAULT 0;
562 aoffset INT DEFAULT 0;
563 blimit INT DEFAULT 0;
564 alimit INT DEFAULT 0;
567 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
570 blimit := pagesize / 2;
573 IF pagesize % 2 <> 0 THEN
574 alimit := alimit + 1;
580 boffset := pagesize / 2;
583 IF pagesize % 2 <> 0 THEN
584 boffset := boffset + 1;
590 -- "bottom" half of the browse results
594 FROM authority.simple_heading ash
595 WHERE ash.atag = ANY (atag_list)
596 AND ash.sort_value < pivot_sort_value
597 ORDER BY ash.sort_value DESC
599 OFFSET ABS(page) * pagesize - boffset
600 ) x ORDER BY row_number DESC;
605 -- "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
612 OFFSET ABS(page) * pagesize - aoffset;
615 $$ LANGUAGE PLPGSQL ROWS 10;
617 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
618 SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
622 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
623 SELECT ARRAY_AGG(y) from (
624 SELECT unnest(ARRAY_CAT(
626 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
628 FROM authority.browse_axis_authority_field_map a
633 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
634 SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
638 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
639 SELECT ARRAY_AGG(y) from (
640 SELECT unnest(ARRAY_CAT(
641 ARRAY[a.authority_field],
642 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
644 FROM authority.control_set_bib_field a
649 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
650 SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1
653 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
654 SELECT ARRAY_AGG(y) from (
655 SELECT unnest(ARRAY_CAT(
657 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
659 FROM authority.control_set_authority_field a
664 CREATE OR REPLACE FUNCTION authority.axis_browse_center( 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($1), $2, $3, $4)
666 $$ LANGUAGE SQL ROWS 10;
668 CREATE OR REPLACE FUNCTION authority.btag_browse_center( 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($1), $2, $3, $4)
670 $$ LANGUAGE SQL ROWS 10;
672 CREATE OR REPLACE FUNCTION authority.atag_browse_center( 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($1), $2, $3, $4)
674 $$ LANGUAGE SQL ROWS 10;
676 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 $$
677 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4)
678 $$ LANGUAGE SQL ROWS 10;
680 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 $$
681 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4)
682 $$ LANGUAGE SQL ROWS 10;
684 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 $$
685 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4)
686 $$ LANGUAGE SQL ROWS 10;
689 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 $$
691 pivot_sort_value TEXT;
694 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
698 -- "bottom" half of the browse results
702 FROM authority.simple_heading ash
703 WHERE ash.atag = ANY (atag_list)
704 AND ash.sort_value < pivot_sort_value
705 ORDER BY ash.sort_value DESC
707 OFFSET (ABS(page) - 1) * pagesize
708 ) x ORDER BY row_number DESC;
713 -- "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
720 OFFSET ABS(page) * pagesize ;
723 $$ LANGUAGE PLPGSQL ROWS 10;
725 CREATE OR REPLACE FUNCTION authority.axis_browse_top( 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($1), $2, $3, $4)
727 $$ LANGUAGE SQL ROWS 10;
729 CREATE OR REPLACE FUNCTION authority.btag_browse_top( 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($1), $2, $3, $4)
731 $$ LANGUAGE SQL ROWS 10;
733 CREATE OR REPLACE FUNCTION authority.atag_browse_top( 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($1), $2, $3, $4)
735 $$ LANGUAGE SQL ROWS 10;
737 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 $$
738 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4)
739 $$ LANGUAGE SQL ROWS 10;
741 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 $$
742 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4)
743 $$ LANGUAGE SQL ROWS 10;
745 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 $$
746 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4)
747 $$ LANGUAGE SQL ROWS 10;
750 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 $$
752 FROM authority.simple_heading ash,
753 public.naco_normalize($2) t(term),
754 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
755 WHERE ash.atag = ANY ($1)
756 AND ash.index_vector @@ ptsq.term
757 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
758 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
759 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
762 $$ LANGUAGE SQL ROWS 10;
764 CREATE OR REPLACE FUNCTION authority.axis_search_rank( 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($1), $2, $3, $4)
766 $$ LANGUAGE SQL ROWS 10;
768 CREATE OR REPLACE FUNCTION authority.btag_search_rank( 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($1), $2, $3, $4)
770 $$ LANGUAGE SQL ROWS 10;
772 CREATE OR REPLACE FUNCTION authority.atag_search_rank( 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($1), $2, $3, $4)
774 $$ LANGUAGE SQL ROWS 10;
776 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 $$
777 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4)
778 $$ LANGUAGE SQL ROWS 10;
780 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 $$
781 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4)
782 $$ LANGUAGE SQL ROWS 10;
784 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 $$
785 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4)
786 $$ LANGUAGE SQL ROWS 10;
789 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 $$
791 FROM authority.simple_heading ash,
792 public.naco_normalize($2) t(term),
793 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
794 WHERE ash.atag = ANY ($1)
795 AND ash.index_vector @@ ptsq.term
796 ORDER BY ash.sort_value
799 $$ LANGUAGE SQL ROWS 10;
801 CREATE OR REPLACE FUNCTION authority.axis_search_heading( 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($1), $2, $3, $4)
803 $$ LANGUAGE SQL ROWS 10;
805 CREATE OR REPLACE FUNCTION authority.btag_search_heading( 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($1), $2, $3, $4)
807 $$ LANGUAGE SQL ROWS 10;
809 CREATE OR REPLACE FUNCTION authority.atag_search_heading( 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($1), $2, $3, $4)
811 $$ LANGUAGE SQL ROWS 10;
813 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 $$
814 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4)
815 $$ LANGUAGE SQL ROWS 10;
817 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 $$
818 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4)
819 $$ LANGUAGE SQL ROWS 10;
821 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 $$
822 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4)
823 $$ LANGUAGE SQL ROWS 10;