begin;
create table change_log (
  change_id    bigint not null default nextval('change_log_seq') primary key,
  table_name   text not null,
  key_value    text not null,
  change_date  timestamptz not null default now()
);

create table change_log_what_changed(
  change_id    bigint not null references change_log on delete cascade,
  column_name  text,
  old_value    text,
  new_value    text,
  primary key  (change_id, column_name)
);

create or replace function create_new_change_log_record( _table_name text, _key_value text) returns bigint as $$
declare _new_change_id bigint;
begin
  _new_change_id := nextval('change_log_seq');
  insert into change_log (change_id, table_name, key_value) values (_new_change_id , _table_name, _key_value);
  return _new_change_id;
end $$ language 'plpgsql';

create or replace function generate_change_log_trigger_proc(_TABLENAME text, _KEYNAME text) returns text as $$
DECLARE
  _proc_text text;
  _rec record;
BEGIN
_proc_text := 'CREATE OR REPLACE FUNCTION track_changes_on_' || _TABLENAME || ' () RETURNS TRIGGER AS $aproc$
DECLARE _chg_id BIGINT;
BEGIN
  _chg_id := create_new_change_log_record( ' || quote_literal(_TABLENAME) || ', text(NEW.' || _KEYNAME || ') );' ;
  FOR _rec IN SELECT attname FROM pg_attribute
                             JOIN pg_class c ON (attrelid = c.oid)
                             WHERE relname = _TABLENAME  AND NOT attisdropped AND attnum >=  0 
              LOOP
  _proc_text := _proc_text || '\nIF NEW.' || _rec.attname || ' <> OLD.' || _rec.attname || ' THEN
    INSERT INTO change_log_what_changed (change_id, column_name, old_value, new_value)
      VALUES ( _chg_id,' || quote_literal(_rec.attname) || ',  OLD.' || _rec.attname ||', NEW.'|| _rec.attname || '); END IF;';
  END LOOP;
  _proc_text := _proc_text || '   RETURN NEW; END $aproc$ language plpgsql; ';
  return  _proc_text;
END$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION start_change_log ( _TABLENAME text, _KEYNAME text) returns void as $$
BEGIN
  EXECUTE generate_change_log_trigger_proc( _TABLENAME, _KEYNAME);
  EXECUTE 'CREATE TRIGGER ' || _TABLENAME ||  '_chg_log_trg AFTER UPDATE ' ||
              ' ON ' || _TABLENAME || ' FOR EACH ROW EXECUTE PROCEDURE track_changes_on_' || _TABLENAME || '()';
  RETURN;
END$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION stop_change_log ( _TABLENAME text, _KEYNAME text) returns void as $$
BEGIN
  EXECUTE 'DROP TRIGGER ' || _TABLENAME ||  '_chg_log_trg ON  ' || _TABLENAME ;
  RETURN;
END$$ LANGUAGE 'plpgsql';

create table t_c_l (id serial, data text, I_data int, f_data float);
insert into t_c_l values ( 1 , 'test 1',  0 , - 1 );
insert into t_c_l values ( 2 , 'test 2',  1 ,  2 );

select  generate_change_log_trigger_proc( 't_c_l', 'id')    ;
select start_change_log('t_c_l', 'id');

update t_c_l set data = 'test 1.1' , f_data =  1 . 5  where id =  1 ;
update t_c_l set data = 'test 2.1' , i_data =  3  where id =  2 ;

select * from change_log join change_log_what_changed using (change_id) order by change_id desc;

update t_c_l set data = 'test 3';

select * from change_log join change_log_what_changed using (change_id) order by change_id desc;
rollback;

BEGIN
CREATE SEQUENCE
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE TABLE
INSERT  74130691   1 
INSERT  74130692   1 
                                                                                       generate_change_log_trigger_proc    
----------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION track_changes_on_t_c_l () RETURNS TRIGGER AS $aproc$
DECLARE _chg_id BIGINT;
BEGIN
  _chg_id := create_new_change_log_record( 't_c_l', text(NEW.id) );
IF NEW.id <> OLD.id THEN
    INSERT INTO change_log_what_changed (change_id, column_name, old_value, new_value)
      VALUES ( _chg_id,'id',  OLD.id, NEW.id); END IF;
IF NEW.data <> OLD.data THEN
    INSERT INTO change_log_what_changed (change_id, column_name, old_value, new_value)
      VALUES ( _chg_id,'data',  OLD.data, NEW.data); END IF;
IF NEW.i_data <> OLD.i_data THEN
    INSERT INTO change_log_what_changed (change_id, column_name, old_value, new_value)
      VALUES ( _chg_id,'i_data',  OLD.i_data, NEW.i_data); END IF;
IF NEW.f_data <> OLD.f_data THEN
    INSERT INTO change_log_what_changed (change_id, column_name, old_value, new_value)
      VALUES ( _chg_id,'f_data',  OLD.f_data, NEW.f_data); END IF;   RETURN NEW; END $aproc$ language plpgsql;
( 1  запись)

 start_change_log
------------------

( 1  запись)

UPDATE  1 
UPDATE  1 
 change_id | table_name | key_value |           change_date           | column_name | old_value | new_value
-----------+------------+-----------+---------------------------------+-------------+-----------+-----------
          2  | t_c_l      |  2          |  05 . 04 . 2006   14 : 17 : 09 . 981291  EEST | data        | test  2     | test  2 . 1 
          2  | t_c_l      |  2          |  05 . 04 . 2006   14 : 17 : 09 . 981291  EEST | i_data      |  1          |  3 
          1  | t_c_l      |  1          |  05 . 04 . 2006   14 : 17 : 09 . 981291  EEST | data        | test  1     | test  1 . 1 
          1  | t_c_l      |  1          |  05 . 04 . 2006   14 : 17 : 09 . 981291  EEST | f_data      | - 1         |  1 . 5 
(записей:  4 )

UPDATE  2 
 change_id | table_name | key_value |           change_date           | column_name | old_value | new_value
-----------+------------+-----------+---------------------------------+-------------+-----------+-----------
          4  | t_c_l      |  2          |  05 . 04 . 2006   14 : 17 : 09 . 981291  EEST | data        | test  2 . 1   | test  3 
          3  | t_c_l      |  1          |  05 . 04 . 2006   14 : 17 : 09 . 981291  EEST | data        | test  1 . 1   | test  3 
          2  | t_c_l      |  2          |  05 . 04 . 2006   14 : 17 : 09 . 981291  EEST | data        | test  2     | test  2 . 1 
          2  | t_c_l      |  2          |  05 . 04 . 2006   14 : 17 : 09 . 981291  EEST | i_data      |  1          |  3 
          1  | t_c_l      |  1          |  05 . 04 . 2006   14 : 17 : 09 . 981291  EEST | data        | test  1     | test  1 . 1 
          1  | t_c_l      |  1          |  05 . 04 . 2006   14 : 17 : 09 . 981291  EEST | f_data      | - 1         |  1 . 5 
(записей:  6 )

ROLLBACK