Implement a handmade change-tracking in PostgreSql

Create two table which you need change-tracking on:

create table my_account (
        id uuid not null primary key,
        username varchar(64) not null unique,
        full_name varchar(128) not null
);

create table my_key_value_pair (
        key varchar(64) not null primary key,
        value varchar(256) not null
);

Create a table to store change-tracking infos

create table row_changes (
        change_id serial primary key,
        row_id text not null,
        change_type text,
        table_name name,
        schema_name name
);

Create a trigger-function to handle table updates

CREATE or replace FUNCTION my_change_tracking() RETURNS trigger AS $ff$
        DECLARE affected_id text;
        BEGIN
                EXECUTE format('SELECT ($1).%s::text', quote_ident(TG_ARGV[0]))
                USING case when NEW is not null then NEW else OLD end
                INTO affected_id;

                insert into row_changes(
                        row_id,
                        change_type,
                        table_name,
                        schema_name
                ) values (
                        affected_id,
                        TG_OP,
                        TG_TABLE_NAME,
                        TG_TABLE_SCHEMA
                );
                RETURN NEW;
        END;
$ff$ LANGUAGE plpgsql;

Register the ‘my_change_tracking’ function as trigger for tables

create trigger my_account_trigger
        after insert
        or update
        or delete
on my_account
FOR EACH ROW execute function my_change_tracking('id')

create trigger my_key_value_pair_trigger
        after insert
        or update
        or delete
on my_key_value_pair
FOR EACH ROW execute function my_change_tracking('key')

Perform Insert / Update / Delete operations

insert into my_account values(gen_random_uuid(), 'pietrom1', 'Pietro Martinelli');
insert into my_account values(gen_random_uuid(), 'pietrom2', 'Pietro Martinelli');
insert into my_account values(gen_random_uuid(), 'pietrom3', 'Pietro Martinelli');
update my_account set full_name = 'Pie Marti' where username = 'pietrom2';
delete from my_account where username = 'pietrom3';
insert into my_account values(gen_random_uuid(), 'pietrom4', 'Pietro Martinelli');
update my_account set full_name = 'Pie Marti' where username = 'pietrom1';

        insert into my_key_value_pair ("key", value) values ('a', 'Uno'),
('b', 'Due'),
('c', 'Tre'),
('d', 'Quattro')

delete from my_key_value_pair where key = 'd'
update my_key_value_pair set value = 'OTTO!' where key = 'c'

Check change-tracking table content

select * from row_changes;