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.

jjanes · Accepted Answer · 2013-10-14 16:22:18Z

25

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.

answered Oct 14, 2013 at 16:22

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Moi Over a year ago

This solution will not work if you've precised to use the trigger only when the value actually changed: ... BEFORE UPDATE OF title ... WHEN (OLD.title IS DISTINCT FROM NEW.title) ...

Fast Engy · Accepted Answer · 2013-10-13 23:26:14Z

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();

Collectives™ on Stack Overflow

Apply postgreSQL trigger to existing rows in database

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related