Update timestamp in postgresSQL after a tuple update-CodePudding

I have a table X with 4 attributes and several tuples of information. One of those attributes called 'Update Date' collects the current timestamp of when those records were introduced to the table.

So, what I would like is to implement a function and trigger that will update the 'Update Date' attribute of a record when a change occurs in that record only, indicating the new CURRENT TIMESTAMP.

For example,

In a record ('Susan', 'Parker', '27',05-05-2020 00:00:00:00), I want that changing the first field 'Susan' will also cause a change in the fourth attribute with the new current timestamp.

I have tried to implement the function as follows:

I imagine that the function must include UPDATE for them to be entered and I have performed the function this way but it is not implemented correctly.

CREATE FUNCTION function()
RETURNS trigger language plpgsql AS $$
BEGIN
    IF (NEW.attribute1 <> OLD.attribute1) THEN
        UPDATE table SET Date_update DEFAULT CURRENT_TIMESTAMP;
    END IF;
    IF (NEW.attribute2 <> OLD.attribute2) THEN
        UPDATE table SET Date_update DEFAULT CURRENT_TIMESTAMP;
    END IF;
    IF (NEW.attribute3 <> OLD.attribute3) THEN
        UPDATE table SET Date_update DEFAULT CURRENT_TIMESTAMP;
    END IF;
    RETURN DEFAULT CURRENT_TIMESTAMP;
END;
$$;

CREATE TRIGGER trigger
AFTER UPDATE ON table
FOR EACH ROW EXECUTE PROCEDURE function();

Thank you in advance

CodePudding user response：

Not tested but it should be something like:

CREATE FUNCTION function()
RETURNS trigger language plpgsql AS $$
BEGIN
    IF (NEW.* <> OLD.*) THEN
        NEW.Date_update = CURRENT_TIMESTAMP;
    END IF;
    
    RETURN NEW;
END;
$$;

CREATE TRIGGER trigger
BEFORE UPDATE ON table
FOR EACH ROW EXECUTE PROCEDURE function();

NEW.* <> OLD.* will test for any change in the NEW tuple vs the OLD one. If there is a change then NEW.Date_update = CURRENT_TIMESTAMP sets the value of Date_update in the NEW tuple and RETURN NEW; returns the modified NEW tuple. Trigger was changed to BEFORE so the RETURN NEW works.