1

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?

3

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.

  • Ah, yes. I was running my commands in SQLWorkbench/J, without autocommit. When I manually do a "commit", or turn on autocommit, it works. Thanks! – Alex Oct 11 '19 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.