Apply postgreSQL trigger to existing rows in database

Question

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.

Answer 1

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.