BEGIN; SELECT evergreen.upgrade_deps_block_check('0942', :eg_version); CREATE OR REPLACE FUNCTION authority.extract_thesaurus( marcxml TEXT ) RETURNS TEXT AS $func$ DECLARE thes_code TEXT; BEGIN thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj'); IF thes_code IS NULL THEN thes_code := '|'; ELSIF thes_code = 'z' THEN thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' ); END IF; RETURN thes_code; END; $func$ LANGUAGE PLPGSQL STABLE STRICT; -- Intended to be used in a unique index on authority.record_entry like so: -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus -- ON authority.record_entry (heading) -- WHERE deleted IS FALSE or deleted = FALSE; CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$ DECLARE acsaf authority.control_set_authority_field%ROWTYPE; tag_used TEXT; nfi_used TEXT; sf TEXT; sf_node TEXT; tag_node TEXT; thes_code TEXT; cset INT; heading_text TEXT; tmp_text TEXT; first_sf BOOL; auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT; BEGIN SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id; IF cset IS NULL THEN SELECT control_set INTO cset FROM authority.control_set_authority_field WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[])) LIMIT 1; END IF; heading_text := ''; FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP tag_used := acsaf.tag; nfi_used := acsaf.nfi; first_sf := TRUE; FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP FOR sf_node IN SELECT unnest(oils_xpath('./*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP tmp_text := oils_xpath_string('.', sf_node); sf := oils_xpath_string('./@code', sf_node); IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN tmp_text := SUBSTRING( tmp_text FROM COALESCE( NULLIF( REGEXP_REPLACE( oils_xpath_string('./@ind'||nfi_used, tag_node), $$\D+$$, '', 'g' ), '' )::INT, 0 ) + 1 ); END IF; first_sf := FALSE; IF tmp_text IS NOT NULL AND tmp_text <> '' THEN heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text; END IF; END LOOP; EXIT WHEN heading_text <> ''; END LOOP; EXIT WHEN heading_text <> ''; END LOOP; IF heading_text <> '' THEN IF no_thesaurus IS TRUE THEN heading_text := tag_used || ' ' || public.naco_normalize(heading_text); ELSE thes_code := authority.extract_thesaurus(marcxml); heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text); END IF; ELSE heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml); END IF; RETURN heading_text; END; $func$ LANGUAGE PLPGSQL STABLE STRICT; ALTER TABLE authority.simple_heading ADD COLUMN thesaurus TEXT; CREATE INDEX authority_simple_heading_thesaurus_idx ON authority.simple_heading (thesaurus); CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$ DECLARE res authority.simple_heading%ROWTYPE; acsaf authority.control_set_authority_field%ROWTYPE; tag_used TEXT; nfi_used TEXT; sf TEXT; cset INT; heading_text TEXT; joiner_text TEXT; sort_text TEXT; tmp_text TEXT; tmp_xml TEXT; first_sf BOOL; auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT; BEGIN SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id; IF cset IS NULL THEN SELECT control_set INTO cset FROM authority.control_set_authority_field WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[])) LIMIT 1; END IF; res.record := auth_id; res.thesaurus := authority.extract_thesaurus(marcxml); FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP res.atag := acsaf.id; tag_used := acsaf.tag; nfi_used := acsaf.nfi; joiner_text := COALESCE(acsaf.joiner, ' '); FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)::TEXT[]) LOOP heading_text := COALESCE( oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml, joiner_text), '' ); IF nfi_used IS NOT NULL THEN sort_text := SUBSTRING( heading_text FROM COALESCE( NULLIF( REGEXP_REPLACE( oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT), $$\D+$$, '', 'g' ), '' )::INT, 0 ) + 1 ); ELSE sort_text := heading_text; END IF; IF heading_text IS NOT NULL AND heading_text <> '' THEN res.value := heading_text; res.sort_value := public.naco_normalize(sort_text); res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value); RETURN NEXT res; END IF; END LOOP; END LOOP; RETURN; END; $func$ LANGUAGE PLPGSQL STABLE STRICT; -- AFTER UPDATE OR INSERT trigger for authority.record_entry CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ DECLARE ashs authority.simple_heading%ROWTYPE; mbe_row metabib.browse_entry%ROWTYPE; mbe_id BIGINT; ash_id BIGINT; BEGIN IF NEW.deleted IS TRUE THEN -- If this authority is deleted DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records DELETE FROM authority.simple_heading WHERE record = NEW.id; -- Should remove matching $0 from controlled fields at the same time? -- XXX What do we about the actual linking subfields present in -- authority records that target this one when this happens? DELETE FROM authority.authority_linking WHERE source = NEW.id OR target = NEW.id; RETURN NEW; -- and we're done END IF; IF TG_OP = 'UPDATE' THEN -- re-ingest? PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled; IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change RETURN NEW; END IF; -- Unless there's a setting stopping us, propagate these updates to any linked bib records PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_auto_update' AND enabled; IF NOT FOUND THEN PERFORM authority.propagate_changes(NEW.id); END IF; DELETE FROM authority.simple_heading WHERE record = NEW.id; DELETE FROM authority.authority_linking WHERE source = NEW.id; END IF; INSERT INTO authority.authority_linking (source, target, field) SELECT source, target, field FROM authority.calculate_authority_linking( NEW.id, NEW.control_set, NEW.marc::XML ); FOR ashs IN SELECT * FROM authority.simple_heading_set(NEW.marc) LOOP INSERT INTO authority.simple_heading (record,atag,value,sort_value,thesaurus) VALUES (ashs.record, ashs.atag, ashs.value, ashs.sort_value, ashs.thesaurus); ash_id := CURRVAL('authority.simple_heading_id_seq'::REGCLASS); SELECT INTO mbe_row * FROM metabib.browse_entry WHERE value = ashs.value AND sort_value = ashs.sort_value; IF FOUND THEN mbe_id := mbe_row.id; ELSE INSERT INTO metabib.browse_entry ( value, sort_value ) VALUES ( ashs.value, ashs.sort_value ); mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS); END IF; INSERT INTO metabib.browse_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id); END LOOP; -- Flatten and insert the afr data PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled; IF NOT FOUND THEN PERFORM authority.reingest_authority_full_rec(NEW.id); PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_rec_descriptor' AND enabled; IF NOT FOUND THEN PERFORM authority.reingest_authority_rec_descriptor(NEW.id); END IF; END IF; RETURN NEW; END; $func$ LANGUAGE PLPGSQL; DROP FUNCTION IF EXISTS authority.atag_search_heading_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.btag_search_heading_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.axis_search_heading_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.atag_search_heading(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.btag_search_heading(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.axis_search_heading(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.simple_heading_search_heading(INT[], TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.atag_search_rank_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.btag_search_rank_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.axis_search_rank_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.atag_search_rank(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.btag_search_rank(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.axis_search_rank(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.simple_heading_search_rank(INT[], TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.atag_browse_top_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.btag_browse_top_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.axis_browse_top_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.atag_browse_top(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.btag_browse_top(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.axis_browse_top(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.simple_heading_browse_top(INT[], TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.atag_browse_center_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.btag_browse_center_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.axis_browse_center_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.atag_browse_center(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.btag_browse_center(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.axis_browse_center(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.simple_heading_browse_center(INT[], TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.simple_heading_find_pivot(INT[], TEXT); -- Support function used to find the pivot for alpha-heading-browse style searching CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT, thesauruses TEXT DEFAULT '' ) RETURNS TEXT AS $$ DECLARE sort_value_row RECORD; value_row RECORD; t_term TEXT; BEGIN t_term := public.naco_normalize(q); SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank, ash.sort_value INTO sort_value_row FROM authority.simple_heading ash WHERE ash.atag = ANY (a) AND ash.sort_value >= t_term AND CASE thesauruses WHEN '' THEN TRUE ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ',')) END ORDER BY rank DESC, ash.sort_value LIMIT 1; SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank, ash.sort_value INTO value_row FROM authority.simple_heading ash WHERE ash.atag = ANY (a) AND ash.value >= t_term AND CASE thesauruses WHEN '' THEN TRUE ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ',')) END ORDER BY rank DESC, ash.sort_value LIMIT 1; IF value_row.rank > sort_value_row.rank THEN RETURN value_row.sort_value; ELSE RETURN sort_value_row.sort_value; END IF; END; $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION authority.simple_heading_browse_center( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ DECLARE pivot_sort_value TEXT; boffset INT DEFAULT 0; aoffset INT DEFAULT 0; blimit INT DEFAULT 0; alimit INT DEFAULT 0; BEGIN pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q,thesauruses); IF page = 0 THEN blimit := pagesize / 2; alimit := blimit; IF pagesize % 2 <> 0 THEN alimit := alimit + 1; END IF; ELSE blimit := pagesize; alimit := blimit; boffset := pagesize / 2; aoffset := boffset; IF pagesize % 2 <> 0 THEN boffset := boffset + 1; END IF; END IF; IF page <= 0 THEN -- "bottom" half of the browse results RETURN QUERY SELECT id FROM ( SELECT ash.id, row_number() over () FROM authority.simple_heading ash WHERE ash.atag = ANY (atag_list) AND CASE thesauruses WHEN '' THEN TRUE ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ',')) END AND ash.sort_value < pivot_sort_value ORDER BY ash.sort_value DESC LIMIT blimit OFFSET ABS(page) * pagesize - boffset ) x ORDER BY row_number DESC; END IF; IF page >= 0 THEN -- "bottom" half of the browse results RETURN QUERY SELECT ash.id FROM authority.simple_heading ash WHERE ash.atag = ANY (atag_list) AND CASE thesauruses WHEN '' THEN TRUE ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ',')) END AND ash.sort_value >= pivot_sort_value ORDER BY ash.sort_value LIMIT alimit OFFSET ABS(page) * pagesize - aoffset; END IF; END; $$ LANGUAGE PLPGSQL ROWS 10; CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.axis_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.btag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.atag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.simple_heading_browse_top( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ DECLARE pivot_sort_value TEXT; BEGIN pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q,thesauruses); IF page < 0 THEN -- "bottom" half of the browse results RETURN QUERY SELECT id FROM ( SELECT ash.id, row_number() over () FROM authority.simple_heading ash WHERE ash.atag = ANY (atag_list) AND CASE thesauruses WHEN '' THEN TRUE ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ',')) END AND ash.sort_value < pivot_sort_value ORDER BY ash.sort_value DESC LIMIT pagesize OFFSET (ABS(page) - 1) * pagesize ) x ORDER BY row_number DESC; END IF; IF page >= 0 THEN -- "bottom" half of the browse results RETURN QUERY SELECT ash.id FROM authority.simple_heading ash WHERE ash.atag = ANY (atag_list) AND CASE thesauruses WHEN '' THEN TRUE ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ',')) END AND ash.sort_value >= pivot_sort_value ORDER BY ash.sort_value LIMIT pagesize OFFSET ABS(page) * pagesize ; END IF; END; $$ LANGUAGE PLPGSQL ROWS 10; CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.axis_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.btag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.atag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.simple_heading_search_rank( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT ash.id FROM authority.simple_heading ash, public.naco_normalize($2) t(term), plainto_tsquery('keyword'::regconfig,$2) ptsq(term) WHERE ash.atag = ANY ($1) AND ash.index_vector @@ ptsq.term AND CASE $5 WHEN '' THEN TRUE ELSE ash.thesaurus = ANY(regexp_split_to_array($5, ',')) END ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC LIMIT $4 OFFSET $4 * $3; $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.axis_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.btag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.atag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.simple_heading_search_heading( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT ash.id FROM authority.simple_heading ash, public.naco_normalize($2) t(term), plainto_tsquery('keyword'::regconfig,$2) ptsq(term) WHERE ash.atag = ANY ($1) AND ash.index_vector @@ ptsq.term AND CASE $5 WHEN '' THEN TRUE ELSE ash.thesaurus = ANY(regexp_split_to_array($5, ',')) END ORDER BY ash.sort_value LIMIT $4 OFFSET $4 * $3; $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.axis_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.btag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.atag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4, $5) $$ LANGUAGE SQL ROWS 10; \qecho \qecho Updating the thesaurus codes in authority.simple_heading; \qecho This may take a while in databases with many authority records. \qecho UPDATE authority.simple_heading a SET thesaurus = authority.extract_thesaurus(b.marc) FROM authority.record_entry b WHERE a.record = b.id; COMMIT;