Thread: Iterate OLD/NEW columns in a trigger?

Is there a way to iterate the columns contained on the OLD/NEW. I want to do
some work in a function in a trigger, but I want it to be generic. I don't
want to have to create a separate trigger for each table, since the work
performed in the trigger is essentially the same for all of them. I just
need to be able to dynamically find the columns associated with OLD/NEW to
make it work. I didn't see anything in the docs about this, anyone have a
solution?

Thanks,
Steve

On Thu, 2005-04-07 at 14:35, Steve - DND wrote:
> Is there a way to iterate the columns contained on the OLD/NEW. I want to do
> some work in a function in a trigger, but I want it to be generic. I don't
> want to have to create a separate trigger for each table, since the work
> performed in the trigger is essentially the same for all of them. I just
> need to be able to dynamically find the columns associated with OLD/NEW to
> make it work. I didn't see anything in the docs about this, anyone have a
> solution?

try pltcl, it's supposed to be pretty good at this.

On Apr 7, 2005, at 3:44 PM, Scott Marlowe wrote:

> On Thu, 2005-04-07 at 14:35, Steve - DND wrote:
>> Is there a way to iterate the columns contained on the OLD/NEW. I
>> want to do
>> some work in a function in a trigger, but I want it to be generic. I
>> don't
>> want to have to create a separate trigger for each table, since the
>> work
>> performed in the trigger is essentially the same for all of them. I
>> just
>> need to be able to dynamically find the columns associated with
>> OLD/NEW to
>> make it work. I didn't see anything in the docs about this, anyone
>> have a
>> solution?
>
> try pltcl, it's supposed to be pretty good at this.
>

As is plperl and likely plpython, and maybe others.

Sean

> > 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.

Thanks,
Steve

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');