CODEWITHSUNDEEP
postgresqltriggerssql-updatetsvector
harijay
harijay

Reputation: 11883

Apply postgreSQL trigger to existing rows in database

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.

Upvotes: 14

Views: 5065

Answers (2)

jjanes
jjanes

Reputation: 44373

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.

Upvotes: 25

Fast Engy
Fast Engy

Reputation: 2011

Normally triggers would run on a table on BEFORE or AFTER an insert, update or delete of a row. There are several options that allow you to decide on when to call the trigger.

Updating the row currently being inserted before insert would be a typical way to use a trigger. Then it is just a matter of creating a trigger on the actual table:

CREATE TRIGGER trig_title_upweight_trigger
  BEFORE INSERT OR UPDATE
  ON pg_rocks_post
  FOR EACH ROW
  EXECUTE PROCEDURE title_upweight_trigger();

Upvotes: 0

Related Questions