postgresql custom TEXT SEARCH CONFIGURATION with tsvector_update_trigger

Question

I would like to use a custom text search configuration with tsvector_update_trigger to be able to update tsv field with french dictionary and unaccent.

So I did that :

CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );
ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;

and then created trigger :

CREATE TRIGGER TS_tsv BEFORE INSERT OR UPDATE ON books FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(tsv, 'pg_catalog.fr', title, authors, descriptionText);

but unfortunately when I want to create or update a row I get an error telling me that fr does not exist.

How I can use this configuration with a trigger ?

Answer 1

When creating the new configuration with

CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );

it does not appear in pg_catalog, but in the current schema (public by default).

This is made apparent by the \dF command in psql:

test=# \dF
 List of text search configurations
 Schema | Name | Description 
------------+------------+---------------------------------------
 pg_catalog | danish | configuration for danish language
 pg_catalog | dutch | configuration for dutch language
 pg_catalog | english | configuration for english language
 pg_catalog | finnish | configuration for finnish language
 pg_catalog | french | configuration for french language
 pg_catalog | german | configuration for german language
 pg_catalog | hungarian | configuration for hungarian language
 pg_catalog | italian | configuration for italian language
 pg_catalog | norwegian | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian | configuration for romanian language
 pg_catalog | russian | configuration for russian language
 pg_catalog | simple | simple configuration
 pg_catalog | spanish | configuration for spanish language
 pg_catalog | swedish | configuration for swedish language
 pg_catalog | turkish | configuration for turkish language
 public | fr | 
(17 rows)

In that case, the trigger should be created with:

CREATE TRIGGER TS_tsv BEFORE INSERT OR UPDATE ON books
 FOR EACH ROW EXECUTE PROCEDURE
 tsvector_update_trigger(tsv, 'public.fr', title, authors, descriptionText);

• Dragouf: Great it works. It was that. Thank you very much Daniel.