I have a search table in postgres with a ts_vector column. It looks like when I insert a dstring to this column is vectorizes it, but it doesn't do any stemming or removal of stop words:
test=# create table sample_ts_vec ( id varchar(255), tsv tsvector);
CREATE TABLE
test=# insert into sample_ts_vec values ('t1234', 'this is a test');
INSERT 0 1
test=# select * from sample_ts_vec;
id | tsv
-------+------------------------
t1234 | 'a' 'is' 'test' 'this'
(1 row)
test=# insert into sample_ts_vec values ('t1235', to_tsvector('this is a test'));
INSERT 0 1
test=# select * from sample_ts_vec;
id | tsv
-------+------------------------
t1234 | 'a' 'is' 'test' 'this'
t1235 | 'test':4
(2 rows)
You'll notice that in the second insert, the 3 stop words are removed, and the word is stemmed (in this case, no stemming necessary), whereas in the first example each word gets added. How can I apply the to_tsvector function automagically to the string value prior to insert?
Jasen's answer was close, but it had a few important errors - here's the corrected version:
CREATE FUNCTION tsvfix() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$
BEGIN
NEW.tsv=to_tsvector(NEW.tsv);
RETURN NEW;
END
$$;
CREATE TRIGGER "tsvfix" BEFORE UPDATE OR INSERT ON "sample_ts_vec" FOR EACH ROW EXECUTE PROCEDURE tsvfix();
Even this doesn't work however. I get an error ERROR: function to_tsvector(tsvector) does not exist