beehive@personal-vm-zhongzhou:~> psql postgres
psql (8.4.58)
Type "help" for help.

postgres=# create language plpgsql;
ERROR:  language "plpgsql" already exists
postgres=#
postgres=# /*data table*/
postgres-# create table public.t (
postgres(#   c1 int primary key,
postgres(#   c2 text,
postgres(#   crt_time timestamp
postgres(# );
/* table to track changes in public.t */
CREATE TABLE public.undo_t (
  id serial8 primary key,
  xid int8,
  relid oid,
  table_schema text,
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
  table_name text,
  when_tg text,
  level text,
CREATE TABLE
postgres=#
postgres=# /* table to track changes in public.t */
postgres-# CREATE TABLE public.undo_t (
postgres(#   id serial8 primary key,
postgres(#   xid int8,
postgres(#   relid oid,
postgres(#   table_schema text,
postgres(#   table_name text,
postgres(#   when_tg text,
postgres(#   level text,
postgres(#   op text,
postgres(#   encoding name,
postgres(#   old_rec public.t,
postgres(#   new_rec public.t,
postgres(#   crt_time timestamp without time zone DEFAULT now(),
postgres(#   username text,
postgres(#   client_addr inet,
postgres(#   client_port int
postgres(# );
NOTICE:  CREATE TABLE will create implicit sequence "undo_t_id_seq" for serial column "undo_t.id"


CREATE OR REPLACE FUNCTION public.undo_t_trace()
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "undo_t_pkey" for table "undo_t"
RETURNS trigger
CREATE TABLE
postgres=#
postgres=#
postgres=# CREATE OR REPLACE FUNCTION public.undo_t_trace()
postgres-# RETURNS trigger
postgres-# LANGUAGE plpgsql
postgres-# AS $BODY$
postgres$# DECLARE
postgres$#   v_new_rec public.t;  -- type cached in session, don't modified when table t altered. must reopen an session.
postgres$#   v_old_rec public.t;  -- type cached in session, don't modified when table t altered. must reopen an session.
postgres$#   v_username text := session_user;
postgres$#   v_client_addr inet := inet_client_addr();
postgres$#   v_client_port int := inet_client_port();
postgres$#   v_xid bigint := txid_current();
postgres$#   v_encoding name := pg_client_encoding();
postgres$# BEGIN
postgres$#   case TG_OP
postgres$#   when 'DELETE' then
postgres$#     v_old_rec := OLD;
postgres$#     insert into public.undo_t (xid, relid, table_schema, table_name, when_tg, level, op, encoding, old_rec, username, client_addr, client_port)
postgres$#       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);
postgres$#   when 'INSERT' then
postgres$#     raise notice '%', NEW;  -- OK
postgres$#     v_new_rec := NEW;
postgres$#     raise notice '%', v_new_rec;  -- use cached type? not OK.
postgres$#     insert into public.undo_t (xid, relid, table_schema, table_name, when_tg, level, op, encoding, new_rec, username, client_addr, client_port)
postgres$#       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);
postgres$#   when 'UPDATE' then
postgres$#     v_old_rec := OLD;
postgres$#     v_new_rec := NEW;
postgres$#     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)
postgres$#       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);
postgres$#     when 'TRUNCATE' then
postgres$#     insert into public.undo_t (xid, relid, table_schema, table_name, when_tg, level, op, encoding, old_rec, username, client_addr, client_port)
postgres$#       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;
postgres$#   else
postgres$#     return null;
postgres$#   end case;
postgres$#     RETURN null;
postgres$# END;
postgres$# $BODY$ strict volatile;
CREATE FUNCTION
postgres=#
postgres=#
postgres=# -- create trigger
postgres=# CREATE TRIGGER tg1 AFTER DELETE or INSERT or UPDATE ON public.t FOR EACH ROW EXECUTE PROCEDURE undo_t_trace();
CREATE TRIGGER
postgres=# CREATE TRIGGER tg2 BEFORE TRUNCATE ON public.t FOR EACH STATEMENT EXECUTE PROCEDURE undo_t_trace();
CREATE TRIGGER
postgres=#
postgres=# -- populate data for test
postgres=# insert into public.t values (1,'test',now());
NOTICE:  (1,test,"2014-09-15 14:15:09.365891")
NOTICE:  (1,test,"2014-09-15 14:15:09.365891")
INSERT 0 1
postgres=# insert into public.t values (2,'test',now());
NOTICE:  (2,test,"2014-09-15 14:15:09.386596")
NOTICE:  (2,test,"2014-09-15 14:15:09.386596")
INSERT 0 1
postgres=#
postgres=# alter table public.t add column c3 int;
ALTER TABLE
postgres=#
postgres=# insert into public.t values (3,'test',now(),1);
NOTICE:  (3,test,"2014-09-15 14:15:09.405794",1)
NOTICE:  (3,test,"2014-09-15 14:15:09.405794",)
INSERT 0 1
postgres=#
postgres=# /* you will see new_rec is missing t.c3 for the new inserted */
postgres-# select new_rec from public.undo_t;
                new_rec
----------------------------------------
 (1,test,"2014-09-15 14:15:09.365891",)
 (2,test,"2014-09-15 14:15:09.386596",)
 (3,test,"2014-09-15 14:15:09.405794",)
(3 rows)

postgres=#
postgres=# \q
beehive@personal-vm-zhongzhou:~> psql postgres
psql (8.4.58)
Type "help" for help.

/* 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);

postgres=#
postgres=# /* exit session and reinsert a tuple, new_rec.c3 can be seen in new inserted tuple */
postgres-# insert into public.t values (4,'test',now(),1);
NOTICE:  (4,test,"2014-09-15 14:15:09.493459",1)
NOTICE:  (4,test,"2014-09-15 14:15:09.493459",1)
INSERT 0 1
postgres=#
postgres=# select new_rec from public.undo_t;
                 new_rec
-----------------------------------------
 (1,test,"2014-09-15 14:15:09.365891",)
 (2,test,"2014-09-15 14:15:09.386596",)
 (3,test,"2014-09-15 14:15:09.405794",)
 (4,test,"2014-09-15 14:15:09.493459",1)
(4 rows)

postgres=#
postgres=# alter table t drop column c2;
ALTER TABLE
postgres=#
postgres=# /* you will see error here */
postgres-# insert into public.t values (5,now(),1);
NOTICE:  (5,"2014-09-15 14:15:09.555769",1)
ERROR:  invalid input syntax for type timestamp: "1"
CONTEXT:  PL/pgSQL function "undo_t_trace" line 17 at assignment
postgres=#
postgres=# \q
beehive@personal-vm-zhongzhou:~> psql postgres
psql (8.4.58)
Type "help" for help.


postgres=#
postgres=# /* without error after relogin a session */
postgres-# insert into public.t values (5,now(),1);
NOTICE:  (5,"2014-09-15 14:15:09.602766",1)
NOTICE:  (5,"2014-09-15 14:15:09.602766",1)
INSERT 0 1
postgres=# select new_rec from public.undo_t;
              new_rec
------------------------------------
 (1,"2014-09-15 14:15:09.365891",)
 (2,"2014-09-15 14:15:09.386596",)
 (3,"2014-09-15 14:15:09.405794",)
 (4,"2014-09-15 14:15:09.493459",1)
 (5,"2014-09-15 14:15:09.602766",1)
(5 rows)

postgres=#
postgres=# drop table t cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table undo_t column old_rec
drop cascades to table undo_t column new_rec
DROP TABLE
postgres=# drop table undo_t;
DROP TABLE
postgres=#
postgres=# \q