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