CODEWITHSUNDEEP
postgresql
Aleksei Khatkevich
Aleksei Khatkevich

Reputation: 2197

Postgres| Create partial GIN index with multiple partial options included in index

Question is regarding partial GIN indexes construction.

I have a following table.

-- 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)
);

I want to create partial GIN index for FTS like this one:

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

Problem is that I need to specify few dozens languages in to_tsvector and in WHERE language = 'language_code'

Is it possible to somehow include few options with a few different (to_tsvector('language_name', text)) / WHERE language = 'language_code' pairs inside the index definition instead of manually repeating yourself like this:

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
…

Thank you.

Upvotes: 0

Views: 1001

Answers (2)

pepeyumpeter
pepeyumpeter

Reputation: 11

@Laurenz Albe answer worked for me as in the following code implementation. Maybe someone facing the same issue finds it helpful (commented lines didn't work for me, but maybe someone can tweak them to work)

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$$;

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246788

Create a simple IMMUTABLE function like this:

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$$;

and create your index like:

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

Upvotes: 1

Related Questions