Editing Corpus/FTS (section)

== Trigger ==

<syntaxhighlight lang="sql">
CREATE FUNCTION corpus.update_content_fts() RETURNS trigger AS $$
DECLARE
    lang text;
    cfg  regconfig;
BEGIN
    -- Skip recomputation if searchable content unchanged (tag-only upserts)
    IF TG_OP = 'UPDATE'
       AND NEW.title IS NOT DISTINCT FROM OLD.title
       AND NEW.body IS NOT DISTINCT FROM OLD.body
       AND NEW.body_search IS NOT DISTINCT FROM OLD.body_search
       AND NEW.language IS NOT DISTINCT FROM OLD.language
       AND NEW.jurisdiction IS NOT DISTINCT FROM OLD.jurisdiction
       AND (NEW.tags->>'summary') IS NOT DISTINCT FROM (OLD.tags->>'summary')
       AND (NEW.tags->>'headnote_classification') IS NOT DISTINCT FROM (OLD.tags->>'headnote_classification') THEN
        NEW.content_fts := OLD.content_fts;
        RETURN NEW;
    END IF;

    -- Extract country prefix for subdivisions (gb-sct → gb, es-ct → es)
    lang := coalesce(NEW.language, split_part(NEW.jurisdiction, '-', 1));

    -- CJK: tsvector non-functional, use pgroonga instead
    IF lang IN ('zh', 'ja', 'ko') THEN
        NEW.content_fts := NULL;
        RETURN NEW;
    END IF;

    cfg := CASE lang
        WHEN 'fr' THEN 'french'::regconfig
        WHEN 'de' THEN 'german'::regconfig
        WHEN 'en' THEN 'english'::regconfig
        WHEN 'es' THEN 'spanish'::regconfig
        WHEN 'it' THEN 'italian'::regconfig
        WHEN 'pt' THEN 'portuguese'::regconfig
        WHEN 'nl' THEN 'dutch'::regconfig
        WHEN 'sv' THEN 'swedish'::regconfig
        WHEN 'da' THEN 'danish'::regconfig
        WHEN 'fi' THEN 'finnish'::regconfig
        WHEN 'hu' THEN 'hungarian'::regconfig
        WHEN 'ro' THEN 'romanian'::regconfig
        WHEN 'tr' THEN 'turkish'::regconfig
        WHEN 'ru' THEN 'russian'::regconfig
        WHEN 'no' THEN 'norwegian'::regconfig
        -- Arabic: no built-in PG config. Falls through to 'simple'.
        -- Install a custom Arabic text search config before ingesting AR data.
        ELSE 'simple'::regconfig
    END;

    -- Binary body check: skip FTS on PDF/binary content
    IF coalesce(NEW.body_search, NEW.body) IS NOT NULL
       AND left(coalesce(NEW.body_search, NEW.body), 4) = '%PDF' THEN
        NEW.content_fts :=
            setweight(to_tsvector(cfg, unaccent(
                corpus.normalize_for_fts(NEW.jurisdiction, coalesce(NEW.title, ''))
            )), 'A') ||
            setweight(to_tsvector(cfg, unaccent(
                coalesce(NEW.tags->>'summary', '') || ' ' ||
                coalesce(NEW.tags->>'headnote_classification', '')
            )), 'B');
        RETURN NEW;
    END IF;

    NEW.content_fts :=
        setweight(to_tsvector(cfg, unaccent(
            corpus.normalize_for_fts(NEW.jurisdiction, coalesce(NEW.title, ''))
        )), 'A') ||
        setweight(to_tsvector(cfg, unaccent(
            coalesce(NEW.tags->>'summary', '') || ' ' ||
            coalesce(NEW.tags->>'headnote_classification', '')
        )), 'B') ||
        setweight(to_tsvector(cfg, unaccent(
            corpus.normalize_for_fts(NEW.jurisdiction,
                regexp_replace(coalesce(NEW.body_search, NEW.body, ''), '<[^>]+>', ' ', 'g'))
        )), 'C');
    RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_content_fts
    BEFORE INSERT OR UPDATE OF title, body, body_search, tags, language, jurisdiction
    ON corpus.documents FOR EACH ROW
    EXECUTE FUNCTION corpus.update_content_fts();
</syntaxhighlight>