Use PostgreSQL function to trigger a task on a table upon row insert or update

PostgreSQL provides support to create function which can then be executed on a table upon a specific event.

Suppose if we want to keep only a specific number of latest rows in the table upon each insertion or update, we can use the trigger function to do that, without the need to run any background clean up task.

Lets see how we can do that

Create a table which has id, value and createdDataTime as its columns

create table test (
  id INT PRIMARY KEY, 
  value VARCHAR(50), 
  createdDateTime TIMESTAMP DEFAULT now()
);

then lets create a PostgreSQL procedure function, which upon execution should keep only the last 5 rows created and drop all the others.

CREATE FUNCTION drop_old_records() RETURNS trigger AS 
    '
    BEGIN
        DELETE FROM test WHERE id IN (SELECT id FROM test ORDER BY createdDateTime DESC OFFSET 5);
        RETURN NEW;
    END;
    '
LANGUAGE plpgsql;

Now the above created procedure function can be configured to trigger it whenever an insert or update is performed on the table test.

CREATE TRIGGER trigger_on_insert_or_update
AFTER INSERT OR UPDATE
ON test
EXECUTE PROCEDURE drop_old_records();

With that when insert or update performed on the table test then the drop_old_records function will be executed and only the last 5 rows will be kept.

Sample insertions

INSERT INTO test (id, value) VALUES (1, 'one');
INSERT INTO test (id, value) VALUES (2, 'two');
INSERT INTO test (id, value) VALUES (3, 'three');
INSERT INTO test (id, value) VALUES (4, 'four');
INSERT INTO test (id, value) VALUES (5, 'five');
INSERT INTO test (id, value) VALUES (6, 'six');

Result

id	value	createddatetime
2	two	2024-02-24T08:35:23.367Z
3	three	2024-02-24T08:35:23.368Z
4	four	2024-02-24T08:35:23.368Z
5	five	2024-02-24T08:35:23.368Z
6	six	2024-02-24T08:35:23.369Z