-- tcl_track_history(TABLE-NAME)
--	Set TABLE-NAME when creating the trigger. Will automatically record change 
--	details in tables history/history_detail
--
CREATE OR REPLACE FUNCTION tcl_track_history() RETURNS trigger AS '
	switch $TG_op {
		DELETE {
			if { [llength [array names OLD cid]] > 0 } {
				set clival $OLD(cid)
			} else {
				set clival "NULL"
			}
			spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'D\')"
		}
		INSERT {
			if { [llength [array names NEW cid]] > 0 } {
				set clival $NEW(cid)
			} else {
				set clival "NULL"
			}
			spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'I\')"
		}
		UPDATE {
			if { [llength [array names OLD cid]] > 0 } {
				set clival $OLD(cid)
			} else {
				set clival "NULL"
			}
			set inserted_main_history_row false
			foreach {col} $TG_relatts {
				# First result seems to be an empty string when stepping through columns
				if { $col > "" } {
					# Check if OLD/NEW contain a value
					if { [llength [array names OLD $col]] > 0 } {
						set oldval $OLD($col)
					} else {
						set oldval "NULL"
					}
					if { [llength [array names NEW $col]] > 0 } {
						set newval $NEW($col)
					} else {
						set newval "NULL"
					}
					if { $oldval != $newval } {
						if { !$inserted_main_history_row } {
							spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'U\')"
							set inserted_main_history_row true
						}
						spi_exec "INSERT INTO history_detail (col,was) VALUES (\'$col\', \'[ quote $oldval ]\')"
					}
				}
			}
		}
	}
	return OK
' LANGUAGE pltcl;

CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client FOR EACH ROW EXECUTE PROCEDURE tcl_track_history('client');