Thread: Referencing columns of the fly in triggers
I would like to know if there is any way to create a common ON UPDATE trigger function that can be called from any table and be able to figure out which field(s) changed. The difficulty is being able to reference at run time the column names of that table, short of hard-coding all of them. The new and old records allow you to reference the columns, but only if you already know the name of the column. Is there nothing equivalent to new[column_index] that would allow me to iterate through the columns without knowing beforehand the column names? And then, given a certain column index, to reference the name of that column?
The goal of this trigger is to log all UPDATES to an audit log table, so the table name, column name, and new column value are all needed to write to the change log. Is there a better way of doing this?
thanks for your help.
James F
On Monday 09 December 2002 21:50, James F. wrote:
> I would like to know if there is any way to create a common ON UPDATE
> trigger function that can be called from any table and be able to figure
> out which field(s) changed. The difficulty is being able to reference at
> run time the column names of that table, short of hard-coding all of them.
> The new and old records allow you to reference the columns, but only if you
> already know the name of the column. Is there nothing equivalent to
> new[column_index] that would allow me to iterate through the columns
> without knowing beforehand the column names? And then, given a certain
> column index, to reference the name of that column?
>
Here is part of my custom replication engine, which is using concept that you
asking for:
/*
* Replication log trigger (RLOG):
*/
PG_FUNCTION_INFO_V1(Rlog);
Datum
Rlog(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
Trigger *trigger; // to get trigger name
HeapTuple tupleNEW = NULL; // tuple to return
HeapTuple tupleOLD = NULL; // OLD tuple
HeapTuple tupleRET = NULL; // OLD tuple
TupleDesc tupdesc; // tuple description
Relation tgRelation; // triggered relation
int spiRet; //Return value for SPI
int curField; // curent field numnber
int qryType=0; // type of the query
PQExpBuffer queryBuff = createPQExpBuffer();
PQExpBuffer cExecStr = createPQExpBuffer();
PQExpBuffer cId = createPQExpBuffer();
PQExpBuffer cIdSrv = createPQExpBuffer();
PQExpBuffer cTableName= createPQExpBuffer();
PQExpBuffer tmpStr = createPQExpBuffer();
PQExpBuffer valStr = createPQExpBuffer();
bool nullField=TRUE;
char* escapedStr;
/* Get NEW and OLD Tuples and tuple description*/
trigger = trigdata->tg_trigger;
tgRelation = trigdata->tg_relation;
tupdesc = tgRelation->rd_att;
tupleNEW = trigdata->tg_newtuple;
tupleOLD = trigdata->tg_trigtuple;
/* if not called by trigger manager return error ? */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "rlog: not fired by trigger manager");
/* if not called for ROW trigger return error*/
if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event))
elog(ERROR, "rlog: can't process STATEMENT events");
/* Connect to SPI manager - if error return*/
if ((spiRet = SPI_connect()) < 0)
elog(ERROR, "rlog: SPI_connect returned %d", spiRet);
/*Get the table name*/
/* Type of SQL statament INSERT,UPDATE or DELETE */
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)){
qryType=1;
tupleRET=tupleOLD;
}else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)){
qryType=2;
tupleRET=tupleNEW;
}else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)){
qryType=3;
tupleRET=tupleNEW;
}
//Get the key for rlog_data
appendPQExpBufferStr(cTableName,SPI_getrelname(tgRelation));
appendPQExpBufferStr(cId,SPI_getvalue(tupleOLD, tupdesc,
SPI_fnumber(tupdesc,"id")));
appendPQExpBufferStr(cIdSrv,SPI_getvalue(tupleOLD, tupdesc,
SPI_fnumber(tupdesc,"id_srv")));
//generete SQL depending on query type
switch( qryType ){
case 1 : { //INSERT TRIGGER
for (curField=1; curField <= tupdesc->natts; curField++){
SPI_getbinval(tupleOLD, tupdesc, curField,&nullField);
if (nullField!=TRUE){
//tmpStr holds the field names
if ( strlen(tmpStr->data) > 0 )
appendPQExpBufferStr(tmpStr,",");
appendPQExpBufferStr(tmpStr,SPI_fname(tupdesc,curField));
//valStr holds the field values
if ( strlen(valStr->data) > 0 )
appendPQExpBufferStr(valStr,",");
appendPQExpBufferStr(valStr,"'");
appendPQExpBufferStr(valStr,SPI_getvalue(tupleOLD, tupdesc,
curField));
appendPQExpBufferStr(valStr,"'");
}
}
printfPQExpBuffer(queryBuff, "INSERT INTO \"%s\" (%s) VALUES (%s)",
cTableName->data,
tmpStr->data,
valStr->data
);
break;}
case 2 : {//UPDATE TRIGGER
bool nullOLD=TRUE;
bool nullNEW=TRUE;
PQExpBuffer VarOLD = createPQExpBuffer();
PQExpBuffer VarNEW = createPQExpBuffer();
for (curField=1; curField <= tupdesc->natts; curField++){
SPI_getbinval(tupleOLD, tupdesc, curField,&nullOLD);
SPI_getbinval(tupleNEW, tupdesc, curField,&nullNEW);
printfPQExpBuffer(VarNEW,"%s",SPI_getvalue(tupleNEW, tupdesc,
curField));
printfPQExpBuffer(VarOLD,"%s",SPI_getvalue(tupleOLD, tupdesc,
curField));
if ( nullNEW && nullOLD ){
//NOTHING TO DO - FIELD UNCHANGED
} else if ( nullNEW ){
//VarNEW is null and field has ben changed !!!
if ( strlen(tmpStr->data) > 0 )
appendPQExpBufferStr(tmpStr,",");
appendPQExpBufferStr(tmpStr,SPI_fname(tupdesc,curField));
appendPQExpBufferStr(tmpStr," = NULL");
} else if ( nullOLD ){
//VarOLD is null and field has ben changed !!!
if ( strlen(tmpStr->data) > 0 )
appendPQExpBufferStr(tmpStr,",");
appendPQExpBufferStr(tmpStr,SPI_fname(tupdesc,curField));
appendPQExpBufferStr(tmpStr," = '");
appendPQExpBufferStr(tmpStr,VarNEW->data);
appendPQExpBufferStr(tmpStr,"'");
}else {
// VarNEW and VarOLD is not null and field has ben changed !!!
if ( (strcmp(VarOLD->data, VarNEW->data)) != 0){ //Is field changed
?
if ( strlen(tmpStr->data) > 0 )
appendPQExpBufferStr(tmpStr,",");
appendPQExpBufferStr(tmpStr,SPI_fname(tupdesc,curField));
appendPQExpBufferStr(tmpStr," = '");
appendPQExpBufferStr(tmpStr,VarNEW->data);
appendPQExpBufferStr(tmpStr,"'");
}
}
}
if (strlen(tmpStr->data)>0){
printfPQExpBuffer(queryBuff, "UPDATE \"%s\" SET %s WHERE id=%s AND
id_srv=%s",
cTableName->data,
tmpStr->data,
cId->data,
cIdSrv->data
);
}else{
queryBuff->data="";
}
break;}
case 3 : { //DELETE TRIGGER
printfPQExpBuffer(queryBuff, "DELETE FROM \"%s\" WHERE id=%s AND
id_srv=%s",
cTableName->data,
cId->data,
cIdSrv->data
);
break;}
}
escapedStr = (char *) calloc( (strlen(queryBuff->data)*2)+1, sizeof(char));
PQescapeString (escapedStr, queryBuff->data, strlen(queryBuff->data));
if (strlen(queryBuff->data)>0){
printfPQExpBuffer(
cExecStr,
"INSERT INTO rlog_data (id,id_srv,relname,query_src) VALUES
('%s','%s','%s','%s')",
cId->data,
cIdSrv->data,
cTableName->data,
escapedStr
);
//elog(LOG, "rlog: %s", cExecStr->data );
SPI_exec(cExecStr->data,1);
}
SPI_finish();
/*Return New*/
return PointerGetDatum(tupleRET);
}
You already got a response in C, here is one in pltcl... I tell it which column is the key and the name of the table
itis pointing at. That could probably be figured out instead of explicitly passed, but I didn't bother. It writes
downall values on insert, the key only on delete, and the key and updated values on update.
create table auditlog (
auditwhen timestamp not null default CURRENT_TIMESTAMP,
auditwhat char(10) not null,
audittable varchar not null,
auditkeyval int not null,
auditfield varchar not null,
oldval text null,
newval text null);
drop function tsp_audit_atrig();
create function tsp_audit_atrig() returns opaque as '
if {[string match $TG_op INSERT]} {
foreach field $TG_relatts {
if {[info exists NEW($field)]} {
set sql "insert into auditlog (auditwhat, audittable, auditkeyval, "
append sql "auditfield, newval) "
append sql "values (''INSERT'', ''$1'', ''$NEW($2)'', ''$field'', "
append sql "''$NEW($field)'')"
spi_exec "$sql"
}
}
} elseif {[string match $TG_op DELETE]} {
foreach field $TG_relatts {
if {[info exists OLD($field)]} {
set sql "insert into auditlog (auditwhat, audittable, auditkeyval, "
append sql "auditfield, oldval) "
append sql "values (''DELETE'', ''$1'', ''$OLD($2)'', ''$field'', "
append sql "''$OLD($field)'')"
spi_exec "$sql"
}
}
} elseif {[string match $TG_op UPDATE]} {
foreach field $TG_relatts {
# Was data changed or is this the key field?
if {([info exists NEW($field)] &&
[info exists OLD($field)] &&
![string match $OLD($field) $NEW($field)])} {
set sql "insert into auditlog (auditwhat, audittable, auditkeyval, "
append sql "auditfield, oldval, newval) "
append sql "values (''UPDATE'', ''$1'', ''$NEW($2)'', ''$field'', "
append sql "''$OLD($field)'', ''$NEW($field)'')"
spi_exec "$sql"
# Is this a field replacing a null?
} elseif {[info exists NEW($field)] && ![info exists OLD($field)]} {
set sql "insert into auditlog (auditwhat, audittable, auditkeyval, "
append sql "auditfield, newval) "
append sql "values (''UPDATE'', ''$1'', ''$NEW($2)'', ''$field'', "
append sql "''$NEW($field)'')"
spi_exec "$sql"
# Is this a field being replaced with null?
} elseif {![info exists NEW($field)] && [info exists OLD($field)]} {
set sql "insert into auditlog (auditwhat, audittable, auditkeyval, "
append sql "auditfield, oldval) "
append sql "values (''UPDATE'', ''$1'', ''$NEW($2)'', ''$field'', "
append sql "''$OLD($field)'')"
spi_exec "$sql"
}
}
}
return "OK"
' language 'pltcl';
drop trigger trig_employeeaudit_atrig on employee;
create trigger trig_employeeaudit_atrig after insert or update or delete on employee
for each row execute procedure tsp_audit_atrig('employee', 'employeeid');
Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
(253) 798-3549
>>> "James F." <nospam_james@hcjb.org.ec> 12/09/02 01:50PM >>>
I would like to know if there is any way to create a common ON UPDATE trigger function that can be called from any
tableand be able to figure out which field(s) changed. The difficulty is being able to reference at run time the column
namesof that table, short of hard-coding all of them. The new and old records allow you to reference the columns, but
onlyif you already know the name of the column. Is there nothing equivalent to new[column_index] that would allow me to
iteratethrough the columns without knowing beforehand the column names? And then, given a certain column index, to
referencethe name of that column?
The goal of this trigger is to log all UPDATES to an audit log table, so the table name, column name, and new column
valueare all needed to write to the change log. Is there a better way of doing this?
thanks for your help.
James F
This seems like a great solution. The key is that in Tcl 'new' and 'old'
are
arrays that are referenced by field name (but the field name can be a
variable). This allows you to compare specific old and new fields at
runtime without knowing beforehand what the field names are. Also the
$TG_relatts auto-created variable is extremely useful.
Are there any plans to implement this same kind of functionality into
pl/pgsql? Or is there already and I just don't know about it?
James F
>
> ----- Original Message -----
> From: "Ian Harding" <ianh@tpchd.org>
> To: <nospam_james@hcjb.org.ec>
> Cc: <pgsql-general@tpchd.org>
> Sent: Tuesday, December 10, 2002 10:02 AM
> Subject: Re: [GENERAL] Referencing columns of the fly in triggers
>
>
> You already got a response in C, here is one in pltcl... I tell it which
> column is the key and the name of the table it is pointing at. That could
> probably be figured out instead of explicitly passed, but I didn't bother.
> It writes down all values on insert, the key only on delete, and the key
and
> updated values on update.
>
> create table auditlog (
> auditwhen timestamp not null default CURRENT_TIMESTAMP,
> auditwhat char(10) not null,
> audittable varchar not null,
> auditkeyval int not null,
> auditfield varchar not null,
> oldval text null,
> newval text null);
>
>
> drop function tsp_audit_atrig();
> create function tsp_audit_atrig() returns opaque as '
>
> if {[string match $TG_op INSERT]} {
> foreach field $TG_relatts {
> if {[info exists NEW($field)]} {
> set sql "insert into auditlog (auditwhat, audittable,
> auditkeyval, "
> append sql "auditfield, newval) "
> append sql "values (''INSERT'', ''$1'', ''$NEW($2)'',
> ''$field'', "
> append sql "''$NEW($field)'')"
> spi_exec "$sql"
> }
> }
> } elseif {[string match $TG_op DELETE]} {
> foreach field $TG_relatts {
> if {[info exists OLD($field)]} {
> set sql "insert into auditlog (auditwhat, audittable,
> auditkeyval, "
> append sql "auditfield, oldval) "
> append sql "values (''DELETE'', ''$1'', ''$OLD($2)'',
> ''$field'', "
> append sql "''$OLD($field)'')"
> spi_exec "$sql"
> }
> }
> } elseif {[string match $TG_op UPDATE]} {
> foreach field $TG_relatts {
> # Was data changed or is this the key field?
>
> if {([info exists NEW($field)] &&
> [info exists OLD($field)] &&
> ![string match $OLD($field) $NEW($field)])} {
> set sql "insert into auditlog (auditwhat, audittable,
> auditkeyval, "
> append sql "auditfield, oldval, newval) "
> append sql "values (''UPDATE'', ''$1'', ''$NEW($2)'',
> ''$field'', "
> append sql "''$OLD($field)'', ''$NEW($field)'')"
> spi_exec "$sql"
>
> # Is this a field replacing a null?
>
> } elseif {[info exists NEW($field)] && ![info exists
> OLD($field)]} {
> set sql "insert into auditlog (auditwhat, audittable,
> auditkeyval, "
> append sql "auditfield, newval) "
> append sql "values (''UPDATE'', ''$1'', ''$NEW($2)'',
> ''$field'', "
> append sql "''$NEW($field)'')"
> spi_exec "$sql"
>
>
> # Is this a field being replaced with null?
>
> } elseif {![info exists NEW($field)] && [info exists
> OLD($field)]} {
> set sql "insert into auditlog (auditwhat, audittable,
> auditkeyval, "
> append sql "auditfield, oldval) "
> append sql "values (''UPDATE'', ''$1'', ''$NEW($2)'',
> ''$field'', "
> append sql "''$OLD($field)'')"
> spi_exec "$sql"
>
> }
> }
> }
>
> return "OK"
>
> ' language 'pltcl';
>
> drop trigger trig_employeeaudit_atrig on employee;
> create trigger trig_employeeaudit_atrig after insert or update or delete
on
> employee
> for each row execute procedure tsp_audit_atrig('employee',
> 'employeeid');
>
>
>
> Ian Harding
> Programmer/Analyst II
> Tacoma-Pierce County Health Department
> iharding@tpchd.org
> (253) 798-3549
>
> >>> "James F." <nospam_james@hcjb.org.ec> 12/09/02 01:50PM >>>
> I would like to know if there is any way to create a common ON UPDATE
> trigger function that can be called from any table and be able to figure
out
> which field(s) changed. The difficulty is being able to reference at run
> time the column names of that table, short of hard-coding all of them. The
> new and old records allow you to reference the columns, but only if you
> already know the name of the column. Is there nothing equivalent to
> new[column_index] that would allow me to iterate through the columns
without
> knowing beforehand the column names? And then, given a certain column
index,
> to reference the name of that column?
>
> The goal of this trigger is to log all UPDATES to an audit log table, so
the
> table name, column name, and new column value are all needed to write to
the
> change log. Is there a better way of doing this?
>
> thanks for your help.
>
> James F
>