CREATE OR REPLACE FUNCTION "public"."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 }

#init PK name and PK value lists
set pk_name_list {}
set pk_value_list {}
set pk_items -1

#set pg_value "10"
#set pl_name "Random"

#Get each of the primary key values into pk_array
spi_exec -array pk_array "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'" {

incr pk_items 1

#get PK name out of the array and place in our list
linsert $pk_name_list $pk_items [string toupper $pk_array(pk_name)]

#get PK value out of array and place it in our list
foreach field $TG_relatts {
if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_array(pk_name)]} {		
linsert $pk_value_list $pk_items [lindex [array get NEW $field] 1]
break;
}
}

}


switch $TG_op {
#log inserted row values
INSERT {
foreach field $TG_relatts {
if {[lsearch $pk_name_list [string toupper [lindex [array get NEW $field] 0]]] < 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_list', '$pk_value_list', '$TG_op', NULL, '$current_value')"
}
}
}

#check changed fields
UPDATE {
foreach field $TG_relatts {
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_list', '$pk_value_list', '$TG_op', '$previous_value', '$current_value')"
}
}
}

#log deleted row
DELETE {
foreach field $TG_relatts {
if {[lsearch $pk_name_list [string toupper [lindex [array get OLD $field] 0]]] < 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_list', '$pk_value_list', '$TG_op', '$previous_value', NULL)"
}
}
}
}		


return OK
$body$
LANGUAGE 'pltcl' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;