Re: Referencing columns of the fly in triggers

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