CODEWITHSUNDEEP
postgresqltriggers
Cody Caughlan
Cody Caughlan

Reputation: 32748

Using tsvector update trigger in Postgres trigger

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?

Upvotes: 15

Views: 11503

Answers (4)

Velerad
Velerad

Reputation: 19

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

Upvotes: 1

Cody Caughlan
Cody Caughlan

Reputation: 32748

This is what I ended up with: 

CREATE FUNCTION albums_vector_update() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        new.search_vector = to_tsvector('pg_catalog.english', COALESCE(NEW.name, ''));
    END IF;
    IF TG_OP = 'UPDATE' THEN
        IF NEW.name <> OLD.name THEN
            new.search_vector = to_tsvector('pg_catalog.english', COALESCE(NEW.name, ''));
        END IF;
    END IF;
    RETURN NEW;
END
$$ LANGUAGE 'plpgsql';


CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON albums
FOR EACH ROW EXECUTE PROCEDURE albums_vector_update();

Upvotes: 16

mhenrixon
mhenrixon

Reputation: 6278

An alternative could be something like distinct from, it really helped me a few times. Untested code though.

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

Upvotes: 4

Gavin
Gavin

Reputation: 6490

The name comparison logic needs to go into the tsvector_update_trigger procedure code. something like:

IF TG_OP = 'UPDATE' THEN
    IF NEW.name <> OLD.name THEN
       -- Do tsvector update
    END IF;
END IF;

Then create a trigger that is called before INSERT and UPDATE

Upvotes: 0

Related Questions