Re: Getting named fields of NEW

On Wednesday 23 April 2003 02:13, Erik Ronström wrote:
> Hello,
>
> I am writing a trigger function, which is supposed to take a field name
> as a parameter (this is because I want to use the same function for
> many tables).
>
> When in an INSERT trigger (firing *before* the insert), you can use the
> NEW parameter to access the row to be inserted. The problem is that I
> don't know the names of the fields. I can't figure out how to use a
> variable (or input parameter in this case) as a field marker, as in
>
> NEW.VarName
>
> The documentation on FOR-IN-EXECUTE doesn't seem to help in this case
>
> :(
>
> Erik
>
Here is part of c trigger function that does something similar you asking for.
Watch close to lines:
for (curField=1; curField <= tupdesc->natts; curField++){
Unfortunately I do not know how to do it with PL/PSQL.

<code>
/*
* 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);
}
</code>