Re: Referencing columns of the fly in triggers - Mailing list pgsql-general
From | Darko Prenosil |
---|---|
Subject | Re: Referencing columns of the fly in triggers |
Date | |
Msg-id | 200212100918.43531.darko.prenosil@finteh.hr Whole thread Raw |
In response to | Referencing columns of the fly in triggers ("James F" <nospam_james@hcjb.org.ec>) |
List | pgsql-general |
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); }
pgsql-general by date: