postgresql - Audit trigger (Statement Level) storing identifier of inserted/updated/deleted rows -

i have following small mvwe basic micro-auditing system works fine lacks functionality:

drop table if exists audit cascade; create table audit(     id          bigserial  not null    ,timevalue   timestamp  not null    ,rolename    name       not null    ,operation   name       not null    ,schemaname  name       not null    ,tablename   name       not null    ,identifiers bigint[]     ---    ,primary key(id) );  -- audit trigger: drop function if exists audit_trigger() cascade; create or replace function audit_trigger() returns trigger $body$ begin  insert audit(timevalue, rolename, operation, schemaname, tablename) values (now()::timestamp, current_user, tg_op, tg_table_schema, tg_relname); return null;  end; $body$ language plpgsql security definer;  -- channels: drop table if exists channels cascade; create table channels(     id          integer    not null    ,userkey     text       not null    ,active      boolean    not null   default(true)     ---    ,primary key(id)    ,unique(userkey) );  create trigger channel_audit_trigger before insert or update or delete on channels each statement execute procedure audit_trigger();  -- perform operations: insert channels( select c.id, 'channel-' || c.id generate_series(1, 300, 10) c(id) );  delete channels id < 10;  update channels set userkey = 'wild channel' id = 21; 

i add last column of audit table, identifiers of rows have been inserted/updated/deleted in channels.

i have used statement level because need collects identifiers in array. not find how access dml statistics. conversely @ row level must handle old , new cases , cannot succeed in aggregate touched identifier.

how can proceed in order fill last column of audit table touched identifiers?

update

finally reached goal, solution might not scalable , may have unwanted drawbacks (i open constructive feedback , advice).

basically, how have solved problem:

1. log @ row level before dml performed table audit_rowlevel;
2. aggregate freshly added content of audit_rowlevel audit_statementlevel @ statement level after dml performed;

minimal working example now:

drop table if exists audit_rowlevel cascade; create table audit_rowlevel(     id          bigserial  not null    ,aggregated  boolean    not null    default(false)    ,timevalue   timestamp  not null     -- https://www.postgresql.org/docs/current/static/functions-info.html    ,rolename    name       not null    ,clientip    inet       not null    ,clientpid   integer    not null     -- https://www.postgresql.org/docs/current/static/plpgsql-trigger.html    ,operation   text       not null    ,schemaname  name       not null    ,tablename   name       not null    ,rowid       bigint     not null     -- https://www.postgresql.org/docs/current/static/functions-json.html    ,oldvalue    jsonb    ,newvalue    jsonb     ---    ,primary key(id) );  -- row level trigger: drop function if exists audit_rowlevel_trigger() cascade; create or replace function audit_rowlevel_trigger() returns trigger $body$ declare  history  boolean := (tg_nargs > 0) , (tg_argv[0]::boolean); rowid    bigint; oldvalue jsonb; newvalue jsonb;  begin   -- handle new: if tg_op = any('{insert,update}')     if history         newvalue := to_jsonb(new);     end if;     rowid := new.id::bigint; end if;  -- handle old: if tg_op = any('{update,delete}')     if history         oldvalue := to_jsonb(old);     end if;     rowid := old.id::bigint; end if;  -- insert: insert audit_rowlevel(timevalue, rolename, clientip, clientpid, operation, schemaname, tablename, rowid, newvalue, oldvalue) values (now()::timestamp, current_user, inet_client_addr(), pg_backend_pid(), tg_op, tg_table_schema, tg_relname, rowid, newvalue, oldvalue);  -- return: if tg_op = any('{insert,update}')     return new; elsif tg_op = 'delete'     return old; else     return null; end if;   end; $body$ language plpgsql security definer;  -- statement level trigger: drop table if exists audit_statementlevel cascade; create table audit_statementlevel(     id          bigserial  not null    ,timevalue   timestamp  not null    ,rolename    name       not null    ,clientip    inet       not null    ,clientpid   integer    not null    ,operation   text       not null    ,schemaname  name       not null    ,tablename   name       not null    ,rowcount    bigint     not null    ,rowids      bigint[]   not null    ,auditids    bigint[]   not null     ---    ,primary key(id) );   -- row level trigger: drop function if exists audit_statementlevel_trigger() cascade; create or replace function audit_statementlevel_trigger() returns trigger $body$ declare  rowcount bigint;  begin     ( select     timevalue, rolename, clientip, clientpid, operation, schemaname, tablename    ,count(*)    ,array_agg(rowid)    ,array_agg(id)     audit_rowlevel     not aggregated group     timevalue, rolename, clientip, clientpid, operation, schemaname, tablename order     timevalue ),  b ( insert audit_statementlevel(timevalue, rolename, clientip, clientpid, operation, schemaname, tablename, rowcount, rowids, auditids) (select * a) returning auditids ),  c ( select array_agg(distinct t.id) ids b, unnest(b.auditids) t(id) )  update     audit_rowlevel set     aggregated = true     c     id = any(c.ids);  return null;  end; $body$ language plpgsql security definer;   -- channels: drop table if exists channels cascade; create table channels(     id          integer    not null    ,userkey     text       not null    ,active      boolean    not null   default(true)     ---    ,primary key(id)    ,unique(userkey) );  create trigger channel_audit_rowlevel_trigger before insert or update or delete on channels each row execute procedure audit_rowlevel_trigger(true);  create trigger channel_audit_statementlevel_trigger after insert or update or delete on channels each statement execute procedure audit_statementlevel_trigger();   -- perform operations: insert channels( select c.id, 'channel-' || c.id generate_series(1, 300, 10) c(id) );  delete channels id < 10;  update channels set userkey = 'wild channel' id = 21; 

i interested knowing if solution looks right professional developers. going direction or solution evil?