Thread: trigger creation error (tsvector_update_trigger)

Hi,

I want to do a full text search on a document. I have created table to store the document. I am getting the following error while creating a trigger

jistest=> CREATE TRIGGER resumes_tsv_update_title_tsvector BEFORE INSERT OR UPDATE ON resumes FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(title_tsvector, 'pg_catalog.english', title);
ERROR:  syntax error at or near "."
LINE 1: ...svector_update_trigger(title_tsvector, 'pg_catalog.english',...

Here is some more information about my db configuration:

jistest=> \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
(16 rows)

jistest=> \dFd
                             List of text search dictionaries
   Schema   |      Name       |                        Description                       
------------+-----------------+-----------------------------------------------------------
 pg_catalog | danish_stem     | snowball stemmer for danish language
 pg_catalog | dutch_stem      | snowball stemmer for dutch language
 pg_catalog | english_stem    | snowball stemmer for english language
 pg_catalog | finnish_stem    | snowball stemmer for finnish language
 pg_catalog | french_stem     | snowball stemmer for french language
 pg_catalog | german_stem     | snowball stemmer for german language
 pg_catalog | hungarian_stem  | snowball stemmer for hungarian language
 pg_catalog | italian_stem    | snowball stemmer for italian language
 pg_catalog | norwegian_stem  | snowball stemmer for norwegian language
 pg_catalog | portuguese_stem | snowball stemmer for portuguese language
 pg_catalog | romanian_stem   | snowball stemmer for romanian language
 pg_catalog | russian_stem    | snowball stemmer for russian language
 pg_catalog | simple          | simple dictionary: just lower case and check for stopword
 pg_catalog | spanish_stem    | snowball stemmer for spanish language
 pg_catalog | swedish_stem    | snowball stemmer for swedish language
 pg_catalog | turkish_stem    | snowball stemmer for turkish language
(16 rows)

Any idea why it is failing?. Appreciate your help.

thanks,
dhanu

dhanunjaya naidu yandrapu <dhanu.techhelp@gmail.com> writes:
> I am getting the following error while creating a trigger

> jistest=> CREATE TRIGGER resumes_tsv_update_title_tsvector BEFORE INSERT OR
> UPDATE ON resumes FOR EACH ROW EXECUTE PROCEDURE
> tsvector_update_trigger(title_tsvector, 'pg_catalog.english', title);
> ERROR:  syntax error at or near "."
> LINE 1: ...svector_update_trigger(title_tsvector, 'pg_catalog.english',...

hmm, it works here.  Are you sure you copied the command correctly?

regression=# create table resumes(title text, title_tsvector tsvector);
CREATE TABLE
regression=# CREATE TRIGGER resumes_tsv_update_title_tsvector BEFORE INSERT OR
regression-# UPDATE ON resumes FOR EACH ROW EXECUTE PROCEDURE
regression-# tsvector_update_trigger(title_tsvector, 'pg_catalog.english', title);
CREATE TRIGGER
regression=# insert into resumes values('the foo bar');
INSERT 0 1
regression=# select * from resumes;
    title    | title_tsvector
-------------+-----------------
 the foo bar | 'bar':3 'foo':2
(1 row)


            regards, tom lane