For the table, an AFTER trigger is supposed to update the tsvector column on insert and update. The assignment works outside the function using an update statement. Also, the notice in the BEGIN block appears in the log as desired. However, the assignment is not made when the trigger fires. Columns in the update or insert are updated; only the fts_vector column is empty.
Logged on user is part of a group that has execute privileges on the function and insert/update privileges on the table.
What might be preventing this from working? All answers and suggestions are welcome.
CREATE TABLE customer
(
name character varying(1000) NOT NULL,
address character varying(1000),
customer_uid serial NOT NULL,
fts_vector tsvector,
CONSTRAINT customer_pkey PRIMARY KEY (customer_uid);
CREATE OR REPLACE FUNCTION vector_update_customer()
RETURNS trigger AS
$BODY$
BEGIN
RAISE NOTICE 'vector_update_customer invoked';
NEW.fts_vector = to_tsvector('english', coalesce(NEW.name,'') || ' ' || coalesce(NEW.address,''));
RETURN NEW;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'vector_update_customer failed';
END
$BODY$
LANGUAGE plpgsql VOLATILE
CREATE TRIGGER trigger_vector_update_customer
AFTER INSERT OR UPDATE
ON customer
FOR EACH ROW
EXECUTE PROCEDURE vector_update_customer();