Re: Iterate OLD/NEW columns in a trigger? - Mailing list pgsql-general

Steve - DND wrote:
>>>try pltcl, it's supposed to be pretty good at this.
>>>
>>
>>As is plperl and likely plpython, and maybe others.
>
>
> Does anyone have an example of this at work? I tried a few Google searches,
> but couldn't get any results showing iterating over the columns of a record.

Attached - example of tcl function that tracks changes to target tables.


--
   Richard Huxton
   Archonet Ltd
-- 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');