PostgreSQL: Create log table and trigger


Mon Aug 29 2022 16:19:20 GMT+0000 (Coordinated Universal Time)

Saved by @marcopinero #sql

create table <tablename>_log(
    id serial primary key,
    idx integer,
    antes json,
    despues json,
    id_m_usuario integer,
    fecha_registro timestamp default now(),
    tipo char(1) default 'U',
    unico varchar(36)

create function f_<tablename>_log()
returns trigger
LANGUAGE plpgsql as
    case tg_op
        when 'INSERT' then
            insert into <tablename>_log (idx, despues, tipo)
            select, row_to_json(new), 'I';
            return new;
        when 'UPDATE' then
            if (.. <condiciones new.campo1 <> new.campo2>... ) then
                insert into <tablename>_log (idx, antes, despues, tipo)
                select, row_to_json(old), row_to_json(new), 'U';
            end if;
            return new;
        when 'DELETE' then
            insert into <tablename>_log (idx, antes, tipo)
            select, row_to_json(old), 'D';
            return old;
    end case;

create trigger t_<tablename>_log
before insert or update or delete
on <tablename>
for each row execute procedure f_<tablename>_log();

Create log table and trigger to auto fill this log table