\nempty_db=# CREATE LANGUAGE plperl;\nCREATE LANGUAGE\nempty_db=# \\! cat plp.sql\nbegin;\nCREATE OR REPLACE FUNCTION perl_watch_changes() RETURNS trigger AS $$\n  foreach $key (keys %{$_TD->{new}} ) {\n    if( $_TD->{new}{$key} ne $_TD->{old}{$key} ) {\n      $old_v = $_TD->{old}{$key};\n      $new_v = $_TD->{new}{$key};\n      elog(NOTICE, qq{$key : $old_v -> $new_v });\n    }\n  }\n  return;\n$$ LANGUAGE plperl;\nCREATE TABLE t001 (\n   id int primary key,\n   t_data text,\n   d_data date,\n   f_data float\n);\nCREATE TRIGGER t001_trg AFTER UPDATE ON t001 FOR EACH ROW EXECUTE PROCEDURE perl_watch_changes();\ninsert into t001 (id, t_data, d_data, f_data) VALUES ( 1 , \'test\', now(),  3 . 1415 );\nupdate t001 set d_data = d_data + \'1 day\'::interval where id =  1 ;\nupdate t001 set f_data =  2 . 78 , t_data = \'new text\';\nrollback;\nempty_db=# \\i plp.sql\nBEGIN\nCREATE FUNCTION\npsql:plp.sql: 17 : NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t001_pkey" for table "t001"\nCREATE TABLE\nCREATE TRIGGER\nINSERT  0   1 \npsql:plp.sql: 20 : NOTICE:  d_data :  2006 - 11 - 17  ->  2006 - 11 - 18 \nUPDATE  1 \npsql:plp.sql: 21 : NOTICE:  f_data :  3 . 1415  ->  2 . 78 \npsql:plp.sql: 21 : NOTICE:  t_data : test -> new text\nUPDATE  1 \nROLLBACK\nempty_db=#\n

/*
create table change_log (
   id        bigserial,
   tablename text,
   fieldname text,
   old_value text,
   new_value text,
   change_date timestamp default now()
);
*/
#include <string.h>
#include "postgres.h"
#include "executor/spi.h"       /* this is what you need to work with SPI */
#include "commands/trigger.h"   /* ... and triggers */
#define GET_TEXT(cstrp) (DirectFunctionCall1(textin, CStringGetDatum(cstrp)))
extern Datum trig_log_changes(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(trig_log_changes);

Datum
trig_log_changes(PG_FUNCTION_ARGS)
{
    TriggerData *trigdata = (TriggerData *) fcinfo->context;
    TupleDesc   tupdesc;
    HeapTuple old_tuple;
    HeapTuple new_tuple;

    Datum argvalues[ 4 ];
    Oid argtypes[ 4 ] = {TEXTOID, TEXTOID, TEXTOID, TEXTOID};
    char nulls[ 4 ] = "    "; // fourspaces
    void * plan;

    int         ret, i;
    char * relname, * fname, * old_value, * new_value;
    int need_to_record;

    /* make sure it is called as a trigger at all */
    if (!CALLED_AS_TRIGGER(fcinfo))
        elog(ERROR, "trig_log_changes: not called by trigger manager");

    if (!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event) || !TRIGGER_FIRED_AFTER(trigdata->tg_event) )
        elog(ERROR, "trig_log_changes: not called after update");

    /* connect to SPI manager */
    if ((ret = SPI_connect()) <  0 )
        elog(ERROR, "trig_log_changes: SPI_connect returned %d", ret);

   tupdesc = trigdata->tg_relation->rd_att;
   old_tuple = trigdata->tg_trigtuple;
   new_tuple = trigdata->tg_newtuple;

   relname = SPI_getrelname( trigdata-> tg_relation );
   argvalues[ 0 ] = GET_TEXT(relname);
   plan = SPI_prepare("INSERT INTO change_log (tablename, fieldname, old_value, new_value) VALUES ( $1, $2, $3, $4 )",  4 , argtypes);
   SPI_saveplan(plan);
   for( i =  0 ; i < tupdesc -> natts; i ++ ) {
      old_value = SPI_getvalue(old_tuple, tupdesc, i);
      new_value = SPI_getvalue(new_tuple, tupdesc, i);
      need_to_record =  0 ;

      if( (NULL == new_value && NULL != old_value) ||
          (NULL != new_value && NULL == old_value) ||
          (NULL != old_value && NULL != new_value &&  0  != strcmp(old_value, new_value))  )
           need_to_record =  1 ;

      if( need_to_record ) {
         fname = SPI_fname( tupdesc, i);

         if( old_value == NULL) nulls[ 2 ] = 'n' ;  else nulls[ 2 ] = ' ';
         if( new_value == NULL) nulls[ 3 ] = 'n' ;  else nulls[ 3 ] = ' ';
         argvalues[ 1 ] = GET_TEXT(fname);
         argvalues[ 2 ] = GET_TEXT(old_value);
         argvalues[ 3 ] = GET_TEXT(new_value);

         elog(NOTICE, "trig_log_changes: %s changed field %s: %s -> %s", relname, fname, old_value, new_value);


         SPI_execp( plan, argvalues, nulls,   1 );
      }
   }
   SPI_freeplan(plan);
   SPI_finish();
   return PointerGetDatum( trigdata->tg_newtuple);
}

begin;
create table change_log (
   id        bigserial,
   tablename text,
   fieldname text,
   old_value text,
   new_value text,
   change_date timestamp default now()
);
CREATE TABLE t001 (
   id int primary key,
   t_data text,
   d_data date,
   f_data float
);

CREATE FUNCTION trig_log_changes() RETURNS trigger  AS '/opt/pg/trigg/trig_log_changes.so'  LANGUAGE C;
CREATE TRIGGER tafter AFTER UPDATE  ON t001 FOR EACH ROW EXECUTE PROCEDURE trig_log_changes();

insert into t001 (id, t_data, d_data, f_data) VALUES ( 1 , 'test', now(),  3 . 1415 );
update t001 set d_data = d_data + '1 day'::interval where id =  1 ;
update t001 set f_data =  2 . 78 , t_data = 'new text';
select * from change_log;
rollback;

empty_db=# \i tst.sql
BEGIN
psql:tst.sql: 9 : NOTICE:  CREATE TABLE will create implicit sequence "change_log_id_seq" for serial column "change_log.id"
CREATE TABLE
psql:tst.sql: 15 : NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t001_pkey" for table "t001"
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT  0   1 
psql:tst.sql: 21 : NOTICE:  trig_log_changes: t001 changed field d_data:  2006 - 11 - 17  ->  2006 - 11 - 18 
UPDATE  1 
psql:tst.sql: 22 : NOTICE:  trig_log_changes: t001 changed field t_data: test -> new text
UPDATE  1 
 id | tablename | fieldname | old_value  | new_value  |        change_date
----+-----------+-----------+------------+------------+----------------------------
   1  | t001      | d_data    |  2006 - 11 - 17  |  2006 - 11 - 18  |  2006 - 11 - 17   14 : 49 : 41 . 487301 
   2  | t001      | t_data    | test       | new text   |  2006 - 11 - 17   14 : 49 : 41 . 487301 
(записей:  2 )

ROLLBACK