Suppose that I have table
create table foo (
insert_current timestamp default now(),
insert_previous timestamp,
bar int primary key,
baz varchar(10)
);
The data will be replaced now and then when fresh batch arrived. I would like to keep track on when previous batch was inserted as well as current timestamp. What would be a good way to do this?
I would create a trigger that automatically sets insert_current and insert_previous on UPDATE statements.
First, create the trigger function:
CREATE OR REPLACE FUNCTION do_update() RETURNS "trigger"
AS $$
BEGIN
NEW.insert_previous := OLD.insert_current;
NEW.insert_current := NOW();
return NEW;
END;
$$
LANGUAGE plpgsql;
Then add the trigger to your table:
CREATE TRIGGER do_update
BEFORE UPDATE ON foo
FOR EACH ROW
EXECUTE PROCEDURE do_update();
When you update a row, copy the value of insert_current to insert_previous and set insert_current to the current timestamp:
UPDATE foo
SET baz = 'whatever',
insert_previous = insert_current,
insert_current = NOW()
WHERE bar = 1;
