Re: Problem with PgTcl auditing function on trigger

Glyn Astill wrote:
> Hi people,
>
>  I've tried posting on the general list about this, but I never get
>  a
>  reply, so I'm trying here.

I think you'll probably have more luck with a TCL list than the PG
hackers list. However, I've attached some pltcl functions I put together
ages ago to do this sort of thing. Hopefully that will help you.

--
   Richard Huxton
   Archonet Ltd
-- History Tracking Trigger-Functions
--
BEGIN;

-- 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');
CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client_keyworkers FOR EACH ROW EXECUTE PROCEDURE
tcl_track_history('client_keyworkers');
CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client_notes FOR EACH ROW EXECUTE PROCEDURE
tcl_track_history('client_notes');

COMMIT;


BEGIN;

CREATE OR REPLACE FUNCTION tcl_track_answers() 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 {
            # Get question title into var $qn_title
            spi_exec "SELECT \'Q\' || qid || \' - \' || title AS qn_title FROM question WHERE qid = $OLD(qid)"

            if { [llength [array names OLD cid]] > 0 } {
                set clival $OLD(cid)
            } else {
                set clival "NULL"
            }

            # Check if OLD/NEW contain a value
            if { [llength [array names OLD text_val]] > 0 } {
                set oldval $OLD(text_val)
            } else {
                set oldval "NULL"
            }
            if { [llength [array names NEW text_val]] > 0 } {
                set newval $NEW(text_val)
            } else {
                set newval "NULL"
            }
            if { $oldval != $newval } {
                spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'U\')"
                spi_exec "INSERT INTO history_detail (col,was) VALUES (\'$qn_title\', \'[ quote $oldval ]\')"
            }
        }
    }
    return OK
' LANGUAGE pltcl;

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

UPDATE client_answer SET text_val = 'partially sighted',ts=now() WHERE aid=20;

COMMIT;