0

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 ?

2

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);
  • Great it works. It was that. Thank you very much Daniel. – Dragouf Feb 25 '16 at 23:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.