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');
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
