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