Postgres: Trigger always uses the same value for now()

Question

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?

Answer 1

Manual:https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

statement_timestamp() / now() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client).

clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.