0

I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?):

create temp table test (
    created  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.updated IS NOT NULL THEN
        NEW.updated := now();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER asdf BEFORE UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

How can I make it set NEW.updated := now(); only when updated is not explicitly set in an update-statement? So that the trigger doesn't overwrite me when I want to set the updated column manually (eg. updated=updated).

0

OLD contains the current values in the table, NEW the values like they would be after the UPDATE.

If NEW.updated IS DISTINCT FROM OLD.updated, it is clear that updated must have been changed by the UPDATE statement (or a previous trigger).

If NEW.updated IS NOT DISTINCT FROM OLD.updated, you cannot know if updated was specified in the UPDATE statement or not — the UPDATE could have set updated to the same value that it had before.

So the best you can get is something like

IF NEW.updated IS NOT DISTINCT FROM OLD.updated THEN
   NEW.updated := current_timestamp;
END IF;

This will not do what you want if you have something like SET updated = updated, but there is no remedy for that.

  • I thought about doing that, but as you said, it doesn't work with updated=updated, which is actually my primary use case for this. For example, a situation like setting a third timestamp column deleted which shouldn't affect updated – dvtan Aug 14 at 14:16
0

A janky solution until something better comes along. Use it like this: if you mean updated=updated, then you write updated=NULL.

create temp table test (
    created  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated  TIMESTAMPTZ
);

CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.updated IS NULL THEN
        IF OLD.updated IS NULL THEN -- first time updating this record
            NEW.updated := now();
        ELSE -- use updated=NULL as workaround for updated=updated
            NEW.updated := OLD.updated;
        END IF;
    ELSE
        NEW.updated := now();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
0

No way to determine does some field was set in UPDATE query text. You may operate with NEW.updated and OLD.updated values only.

Actions table:

OLD.updated    NEW.updated    Action
------------------------------------
NULL           NULL           nothing
NULL           now()          nothing
NULL           NOT now()      nothing
now()          NULL           NEW.updated=OLD.updated
now()          now()          nothing
now()          NOT now()      ?
NOT now()      NULL           NEW.updated=OLD.updated
NOT now()      now()          NEW.updated=OLD.updated
NOT now()      NOT now()      NEW.updated=OLD.updated

And compacted form:

OLD.updated    NEW.updated    Action
------------------------------------
NULL           any value      nothing
NOT now()      any value      NEW.updated=OLD.updated
now()          NULL           NEW.updated=OLD.updated
now()          now()          nothing
now()          NOT now()      ?

Define the action unknown to me. Then convert this table to proper If-ElseIf-Else form...

PS. now() means that the difference between field value and current timestamp is negligible small.

--

UPDATE

The final expression will be like:

IF   (OLD.updated != NOW()) 
  OR (     (OLD.updated = NOW()) 
       AND (NEW.updated IS NULL)
     ) 
THEN SET NEW.updated=OLD.updated
ELSEIF     (OLD.updated = NOW()) 
       AND (NEW.updated != NOW()) 
THEN ?????
END IF;

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.