assignment in trigger function does not work

Question

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

Answer 1

I found that changing the table trigger to a BEFORE trigger allowed the function to work as desired. While I am glad that it works and now understand that NEW is only valid in a BEFORE trigger, I am left with a question: what is the correct way to refer to the inserted or updated row in an AFTER trigger?

• user1822: new and old are the correct way to reference them in any trigger (including an AFTER trigger). However you can not make any changes to those values because - well - it's after those changes have been persisted. Assigning values does work - for the duration of the trigger, but those modifications are not stored in the table
• Mophilly: Having thought about this, and reading your comment, I understand that the references are valid but (as you wrote) the operation has already completed. Any further changes required will need another statement of some kind.