Блог

Вопрос касается построения частичных индексов GIN.

У меня есть следующая таблица.

 -- auto-generated definition
create table archives_subtitles
(
    id             serial     not null
        constraint archives_subtitles_pkey
            primary key,

    episode_number smallint   not null
        constraint archives_subtitles_episode_number_check
            check (episode_number >= 0),

    text           text       not null,

    language       varchar(2) not null

    season_id      integer    not null
        constraint archives_subtitles_season_id_e3690b93_fk_archives_
            references archives_seasonmodel
            deferrable initially deferred,
    constraint archives_subtitles_season_id_episode_number_fec69da8_uniq
        unique (season_id, episode_number, language)
);



  

Я хочу создать частичный GIN индекс для FTS , подобный этому:

 CREATE INDEX CONCURRENTLY IF NOT EXISTS test ON archives_subtitles
    USING GIN
    (to_tsvector('english', text))
    WITH (fastupdate = off)
    WHERE language = 'en'
;

  

Проблема в том, что мне нужно указать несколько десятков языков в to_tsvector и в WHERE language = 'language_code'

Возможно ли каким-то образом включить несколько параметров с несколькими разными (to_tsvector('language_name', text)) / WHERE language = 'language_code' парами внутри определения индекса вместо того, чтобы вручную повторять подобное:

 CREATE INDEX CONCURRENTLY IF NOT EXISTS test1 ON archives_subtitles
    USING GIN
    (to_tsvector('english', text))
    WITH (fastupdate = off)
    WHERE language = 'en'
;
CREATE INDEX CONCURRENTLY IF NOT EXISTS test2 ON archives_subtitles
    USING GIN
    (to_tsvector('french', text))
    WITH (fastupdate = off)
    WHERE language = 'fr'
;
CREATE INDEX CONCURRENTLY IF NOT EXISTS test3 ON archives_subtitles
    USING GIN
    (to_tsvector('russian', text))
    WITH (fastupdate = off)
    WHERE language = 'ru'
;
etc
etc
etc
…
  

Спасибо.

Создайте простую IMMUTABLE функцию, подобную этой:

 CREATE FUNCTION lang_to_tsconfig(text) RETURNS regconfig
   LANGUAGE sql IMMUTABLE STRICT AS
$$SELECT CASE WHEN $1 = 'en' THEN 'english'::regconfig
              WHEN $1 = 'de' THEN 'german'::regconfig
              ...
         END$$;
  

и создайте свой индекс, подобный:

 CREATE INDEX ON archives_subtitles USING gin (
   to_tsvector(lang_to_tsconfig(language), text))
   WITH (fastupdate = off);
  

ответ @Laurenz Albe сработал для меня, как и в следующей реализации кода. Возможно, кто-то, столкнувшийся с такой же проблемой, найдет это полезным (строки с комментариями у меня не сработали, но, возможно, кто-то может настроить их для работы)

 CREATE FUNCTION lang_to_tsconfig(text) RETURNS regconfig
   LANGUAGE sql IMMUTABLE STRICT AS
$$SELECT CASE
WHEN $1 = 'ar' THEN 'arabic'::regconfig
-- WHEN $1 = 'be' THEN 'belarusian'::regconfig
-- WHEN $1 = 'bg' THEN 'bulgarian'::regconfig
-- WHEN $1 = 'bs' THEN 'bosnian'::regconfig
-- WHEN $1 = 'cs' THEN 'czech'::regconfig
WHEN $1 = 'da' THEN 'danish'::regconfig
WHEN $1 = 'de' THEN 'german'::regconfig
--WHEN $1 = 'el' THEN 'greek'::regconfig
WHEN $1 = 'en' THEN 'english'::regconfig
WHEN $1 = 'es' THEN 'spanish'::regconfig
--WHEN $1 = 'et' THEN 'estonian'::regconfig
WHEN $1 = 'fi' THEN 'finnish'::regconfig
WHEN $1 = 'fr' THEN 'french'::regconfig
--WHEN $1 = 'he' THEN 'hebrew'::regconfig
--WHEN $1 = 'hr' THEN 'croatian'::regconfig
WHEN $1 = 'hu' THEN 'hungarian'::regconfig
WHEN $1 = 'id' THEN 'indonesian'::regconfig
--WHEN $1 = 'is' THEN 'icelandic'::regconfig
WHEN $1 = 'it' THEN 'italian'::regconfig
--WHEN $1 = 'ja' THEN 'japanese'::regconfig
--WHEN $1 = 'ko' THEN 'korean'::regconfig
WHEN $1 = 'lt' THEN 'lithuanian'::regconfig
--WHEN $1 = 'lv' THEN 'latvian'::regconfig
WHEN $1 = 'nl' THEN 'dutch'::regconfig
WHEN $1 = 'no' THEN 'norwegian'::regconfig
--WHEN $1 = 'pl' THEN 'polish'::regconfig
WHEN $1 = 'pt' THEN 'portuguese'::regconfig
WHEN $1 = 'ro' THEN 'romanian'::regconfig
WHEN $1 = 'ru' THEN 'russian'::regconfig
--WHEN $1 = 'sk' THEN 'slovak'::regconfig
--WHEN $1 = 'sl' THEN 'slovenian'::regconfig
--WHEN $1 = 'sr' THEN 'serbian'::regconfig
WHEN $1 = 'sv' THEN 'swedish'::regconfig
WHEN $1 = 'tr' THEN 'turkish'::regconfig
--WHEN $1 = 'uk' THEN 'ukrainian'::regconfig
--WHEN $1 = 'vi' THEN 'vietnamese'::regconfig
--WHEN $1 = 'zh' THEN 'chinese'::regconfig
         END$$;