I am using PostgeSQL 9.2.2. My database schema is
pg_rocks_post
title | character varying(1024) | not null
body | text | not null
body_title_tsv | tsvector |
body_title_titleupweight_tsv | tsvector |
I created the body_title_titleupweight_tsv as a type tsvector. I then defined a trigger using the examples in the documentation which up weighted the title as follows.
pgdj=# CREATE FUNCTION title_upweight_trigger() RETURNS trigger AS $$
begin
new.body_title_titleupweight_tsv :=
setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') ||
setweight(to_tsvector('pg_catalog.english', coalesce(new.body,'')), 'D');
return new;
end
$$ LANGUAGE plpgsql;
I know the trigger works because when I update an entry in the pg_rocks_post and then query it : I see that it has correctly populated the body_title_titleupweight_tsv ts_vector with that updated row.
My Question is how do I have it apply the trigger to the existing rows in my table. I am only learning postgres and so have a few hundred entries in my test database and want to know how to populate the body_title_titleupweight_tsv column.
I think one way to do this would be to run an update and write the function all over with something like
pgdj=# UPDATE pg_rocks_post SET body_title_titleupweight_tsv =
setweight(to_tsvector( coalesce(title,'')),'A') ||
setweight(to_tsvector(coalesce(body,'')),'D');
Instead of re writing the logic for the trigger again in the update statement above. Is there a way to trigger the trigger above by doing a dummy update or a "touch" style operation that flips the trigger on all rows in the database.
I tried looking for syntax or examples of such dummy or "touch" type operations and could not find any that explained how to do this.
Since the table is small, just do a dummy update of the entire table:
update pg_rocks_post set title=title;
And let the trigger do its thing.