I would like to create a trigger that keeps a last-updated time on each row. It would fire on CREATE and UPDATE, and store the current time into a column. I'm following basically the exact recipe from here: https://www.postgresql.org/docs/9.2/plpgsql-trigger.html
When I run it, the value in the column is always the same, no matter how many times I update the field. It's as if 'now()' was only evaluated once, and the value was cached.
Here is my code:
CREATE FUNCTION alex_trigger() RETURNS trigger AS $alex_trigger$
BEGIN
NEW.v := now();
RETURN NEW;
END;
$alex_trigger$ LANGUAGE plpgsql;
CREATE TRIGGER alex_trigger BEFORE INSERT OR UPDATE ON alex
FOR EACH ROW EXECUTE PROCEDURE alex_trigger();
I must be doing something dumb. Any ideas?