-- =========================================================

CREATE TABLE audit_table
(
  ts timestamp with time zone,
  usr character varying(20),
  tbl character varying(20),
  fld character varying(20),
  pk_name character varying(20),
  pk_value character varying(40),
  mod_type character(6),
  old_val text,
  new_val text
);

-- =========================================================

-- Function: log_to_audit_table()
-- DROP FUNCTION log_to_audit_table();

CREATE OR REPLACE FUNCTION log_to_audit_table()
  RETURNS "trigger" AS
$BODY$
spi_exec "SELECT CURRENT_USER AS tguser"
spi_exec "SELECT relname AS tgname FROM pg_class WHERE relfilenode = $TG_relid"

#skip changes on audit_table
if {[string equal -nocase $tgname audit_table]} { return OK }

#get PK name
set pk_name ""
spi_exec "SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a, pg_index i
 WHERE c.relname = '$tgname'
 AND c.oid=i.indrelid
 AND a.attnum > 0
 AND a.attrelid = i.indexrelid
 AND i.indisprimary='t'"

switch $TG_op {
INSERT {
  set pk_value ""

  #get PK value
  foreach field $TG_relatts {
    if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
      set pk_value [lindex [array get NEW $field] 1]
      break;
    }
  }
  #log inserted row values
  foreach field $TG_relatts {
    if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name] == 0} {
      set modified_field [lindex [array get NEW $field] 0]
      set current_value [lindex [array get NEW $field] 1]
      spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld, pk_name, pk_value, mod_type, old_val, new_val)
        VALUES (CURRENT_TIMESTAMP, '$tguser', '$tgname', '$modified_field', '$pk_name', '$pk_value', '$TG_op', NULL, '$current_value')"
    }
  }
}
UPDATE {
  set pk_value ""

  #get PK value
  foreach field $TG_relatts {
    if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
      set pk_value [lindex [array get NEW $field] 1]
      break;
    }
  }
  #log updated row values
  foreach field $TG_relatts {
    #check changed fields
    if {[string equal -nocase [array get NEW $field] [array get OLD $field]] == 0} {
      set modified_field [lindex [array get OLD $field] 0]
      if {[string compare $modified_field ""] == 0} {
        set modified_field [lindex  [array get NEW $field] 0]
      }
      set previous_value [lindex [array get OLD $field] 1]
      set current_value  [lindex [array get NEW $field] 1]
      spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld, pk_name, pk_value, mod_type, old_val, new_val)
        VALUES (CURRENT_TIMESTAMP, '$tguser', '$tgname', '$modified_field', '$pk_name', '$pk_value', '$TG_op', '$previous_value', '$current_value')"
    }
  }
}
DELETE {
  set pk_value ""

  #get PK value
  foreach field $TG_relatts {
    if {[string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} {
      set pk_value [lindex [array get OLD $field] 1]
      break;
    }
  }
  #log deleted row values
  foreach field $TG_relatts {
    if {[string equal -nocase [lindex [array get OLD $field] 0] $pk_name] == 0} {
      set modified_field [lindex [array get OLD $field] 0]
      set previous_value [lindex [array get OLD $field] 1]
      spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld, pk_name, pk_value, mod_type, old_val, new_val)
        VALUES (CURRENT_TIMESTAMP, '$tguser', '$tgname', '$modified_field', '$pk_name', '$pk_value', '$TG_op', '$previous_value', NULL)"
    }
  }
}
}
return OK
$BODY$
  LANGUAGE 'pltcl' VOLATILE;


-- =========================================================

-- DROP TABLE test1;

CREATE TABLE test1
(
  id integer NOT NULL,
  somename character varying(50),
  somevalue integer,
  somets timestamp without time zone,
  CONSTRAINT test1_pkey PRIMARY KEY (id)
);

-- Trigger: tg_audit_test1 on test1
-- DROP TRIGGER tg_audit_test1 ON test1;

CREATE TRIGGER tg_audit_test1
  AFTER INSERT OR UPDATE OR DELETE
  ON test1
  FOR EACH ROW
  EXECUTE PROCEDURE log_to_audit_table();


-- =========================================================

-- DROP TABLE test2;
CREATE TABLE test2
(
  pkid integer NOT NULL,
  testname text,
  testvalue real,
  testdate date,
  CONSTRAINT test2_pkey PRIMARY KEY (pkid)
);

-- Trigger: tg_audit_test2 on test2
-- DROP TRIGGER tg_audit_test2 ON test2;

CREATE TRIGGER tg_audit_test2
  AFTER INSERT OR UPDATE OR DELETE
  ON test2
  FOR EACH ROW
  EXECUTE PROCEDURE log_to_audit_table();


-- =========================================================