create language plpgsql;

/*data table*/
create table public.t (
  c1 int primary key,
  c2 text,
  crt_time timestamp
);

/* table to track changes in public.t */
CREATE TABLE public.undo_t (
  id serial8 primary key,
  xid int8,
  relid oid,
  table_schema text,
  table_name text,
  when_tg text,
  level text,
  op text,
  encoding name,
  old_rec public.t,  
  new_rec public.t,  
  crt_time timestamp without time zone DEFAULT now(),
  username text,
  client_addr inet,
  client_port int
);


CREATE OR REPLACE FUNCTION public.undo_t_trace()
RETURNS trigger
LANGUAGE plpgsql
AS $BODY$
DECLARE
  v_new_rec public.t;  -- type cached in session, don't modified when table t altered. must reopen an session.
  v_old_rec public.t;  -- type cached in session, don't modified when table t altered. must reopen an session.
  v_username text := session_user;
  v_client_addr inet := inet_client_addr();
  v_client_port int := inet_client_port();
  v_xid bigint := txid_current();
  v_encoding name := pg_client_encoding();
BEGIN
  case TG_OP
  when 'DELETE' then 
    v_old_rec := OLD;
    insert into public.undo_t (xid, relid, table_schema, table_name, when_tg, level, op, encoding, old_rec, username, client_addr, client_port)
      values (v_xid, tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_encoding, v_old_rec, v_username, v_client_addr, v_client_port);
  when 'INSERT' then 
    raise notice '%', NEW;  -- OK
    v_new_rec := NEW;
    raise notice '%', v_new_rec;  -- use cached type? not OK.
    insert into public.undo_t (xid, relid, table_schema, table_name, when_tg, level, op, encoding, new_rec, username, client_addr, client_port)
      values (v_xid, tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_encoding, v_new_rec, v_username, v_client_addr, v_client_port);
  when 'UPDATE' then 
    v_old_rec := OLD;
    v_new_rec := NEW;
    insert into public.undo_t (xid, relid, table_schema, table_name, when_tg, level, op, encoding, old_rec, new_rec, username, client_addr, client_port)
      values (v_xid, tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_encoding, v_old_rec, v_new_rec, v_username, v_client_addr, v_client_port);
    when 'TRUNCATE' then 
    insert into public.undo_t (xid, relid, table_schema, table_name, when_tg, level, op, encoding, old_rec, username, client_addr, client_port)
      select v_xid, tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_encoding, t, v_username, v_client_addr, v_client_port from public.t;
  else
    return null;
  end case;
    RETURN null;
END;
$BODY$ strict volatile;


-- create trigger
CREATE TRIGGER tg1 AFTER DELETE or INSERT or UPDATE ON public.t FOR EACH ROW EXECUTE PROCEDURE undo_t_trace();
CREATE TRIGGER tg2 BEFORE TRUNCATE ON public.t FOR EACH STATEMENT EXECUTE PROCEDURE undo_t_trace();

-- populate data for test
insert into public.t values (1,'test',now());
insert into public.t values (2,'test',now());

alter table public.t add column c3 int;

insert into public.t values (3,'test',now(),1);

/* you will see new_rec is missing t.c3 for the new inserted */
select new_rec from public.undo_t;

\q
psql postgres

/* exit session and reinsert a tuple, new_rec.c3 can be seen in new inserted tuple */
insert into public.t values (4,'test',now(),1);

select new_rec from public.undo_t;

alter table t drop column c2;

/* you will see error here */
insert into public.t values (5,now(),1);

\q
psql postgres

/* without error after relogin a session */
insert into public.t values (5,now(),1);
select new_rec from public.undo_t;

drop table t cascade;
drop table undo_t;