Using tsvector update trigger in Postgres trigger

user_25398 0
Viewed 9676

I have a tsvector column that I want to update when the row changes. For an INSERT I am using this trigger:

CREATE TRIGGER albums_vector_insert BEFORE INSERT
ON albums
FOR EACH ROW EXECUTE PROCEDURE
 tsvector_update_trigger('search_vector', 'pg_catalog.english', 'name')

Which works fine, seemingly. I would like to use another trigger on UPDATE clauses, but I only want it to fire when the name actually changes, so I dont waste cycles updating the search vector needlessly. I've tried this:

CREATE TRIGGER albums_vector_update BEFORE UPDATE ON albums
FOR EACH ROW EXECUTE PROCEDURE
    IF NEW.name <> OLD.name THEN
        tsvector_update_trigger(search_vector, 'pg_catalog.english', name);
    END IF;

But this throws 2 errors when I try to create the trigger:

Error : ERROR:  syntax error at or near "NEW"
LINE 3:  IF NEW.name <> OLD.name THEN
            ^
Error : ERROR:  syntax error at or near "IF"
LINE 1: END IF
            ^

From my understanding, if I use the trigger procedure syntax, ala:

CREATE OR REPLACE FUNCTION something() RETURNS TRIGGER

then associate my function with a trigger, then I wont be able to use the built-in tsvector_update_trigger function and will need to handle the ts_vector manipulation myself. Hence my trying to use the all-in-one-trigger+procedure syntax...

Any ideas?

triggers postgresql
4 Answers

When you insert row you don't have to check this condition, actually you can't as there is no OLD record so:

CREATE TRIGGER albums_vector_insert 
BEFORE INSERT ON albums
FOR EACH ROW 
EXECUTE PROCEDURE tsvector_update_trigger(search_vector, 'pg_catalog.english', name);

For update:

CREATE TRIGGER albums_vector_update 
BEFORE UPDATE ON albums
FOR EACH ROW 
WHEN (OLD.name IS DISTINCT FROM NEW.name)
EXECUTE PROCEDURE tsvector_update_trigger(search_vector, 'pg_catalog.english', name);
user_99367840
Related