Thread: help needed on SPI_modifytuple.
I'm trying to sipmply modify a tuple before it will be inserted in the db.
The problem is that when it try to insert the modified tuple with
SPI_modifytuple all i get is a SPI_ERROR_ARGUMENT negative (-6) .
Could someone help me? i'm reallygoing crazy.
What i did is just a fire-before C trigger that acts like this:
#include <postgres.h>
#include <executor/spi.h> /* this is what you need to work with SPI */
#include <commands/trigger.h> /* ... and triggers */
#include <string.h>
#include <utils/builtins.h>
extern Datum trgupdana(PG_FUNCTION_ARGS);
char *checkFieldData(char *);
PG_FUNCTION_INFO_V1(trgupdana);
Datum
trgupdana(PG_FUNCTION_ARGS)
{ TriggerData *trigdata = (TriggerData *) fcinfo->context; TupleDesc tupdesc; HeapTuple rettuple,
oldtuple, newtuple; char *rs1,*rs2,*rs3,*relname; int ret, i,j; bool isnull;
Relation rel;
/* make sure it's called as a trigger at all */ if (!CALLED_AS_TRIGGER(fcinfo)) elog(ERROR, "trgchkneg: not
called
by trigger manager\n");
if (TRIGGER_FIRED_BEFORE(trigdata->tg_event) &&
TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) //trigger fires when called
for rows and before updating { tupdesc = trigdata->tg_relation->rd_att; oldtuple =
trigdata->tg_trigtuple;newtuple = trigdata->tg_newtuple; rettuple=NULL;rel = trigdata->tg_relation;relname=
SPI_getrelname(rel); if ((ret = SPI_connect()) < 0) // SPI
manager initialization elog(NOTICE, "trgCheckNeg : SPI_connect returned: %d", ret);
rs1=SPI_getvalue(oldtuple,tupdesc,5); rs2=SPI_getvalue(oldtuple,tupdesc,6); elog(NOTICE,"%s,%s",rs1,rs2);
int attnum; Datum new_value; rs3=(char*) malloc(80); sprintf(rs3,"");if(rs1[strlen(rs1)-1]=='&'){
rs3=strncat(rs3,rs1,strlen(rs1)-1); rs3=strcat(rs3,"E ");} else if(rs1[strlen(rs1)-1]==','){
rs3=strncat(rs3,rs1,strlen(rs1)-1); rs3=strcat(rs3,", ");} else { rs3=rs1; }
elog(NOTICE,"1:%s",rs3); if(strlen(rs2)!=0){
rs3=strcat(rs3,rs2);}elog(NOTICE,"2:%s",rs3);new_value=DirectFunctionCall1(textin,PointerGetDatum(checkFieldData(rs3)));
attnum=SPI_fnumber(tupdesc,"ARGSL1"); if(rel==NULL) elog(NOTICE,"rel NULL");if(&attnum==NULL)
elog(NOTICE,"attnumNULL");if(&new_value==NULL) elog(NOTICE,"new_value NULL");if(&isnull==NULL) elog(NOTICE,"isnull
NULL"); rettuple=SPI_modifytuple(rel,newtuple,1,&attnum,&new_value,&isnull);if(rettuple==NULL){
elog(ERROR,"trgupdana(%s):%d returned by
SPI_modifytuple",relname,SPI_result);}return PointerGetDatum(rettuple); } return PointerGetDatum(oldtuple);
}
char *checkFieldData(char *valueToCheck){int i=0;for(i=0;i<strlen(valueToCheck);i++){ if(valueToCheck[i]=='&')
valueToCheck[i]='E'; else if(valueToCheck[i]=='\'') valueToCheck[i]=96;} return(valueToCheck);
}
--
http://www.fabpicca.net
On Mon, Sep 12, 2005 at 04:17:33PM +0200, fabrizio picca wrote: > I'm trying to sipmply modify a tuple before it will be inserted in the db. > The problem is that when it try to insert the modified tuple with > SPI_modifytuple all i get is a SPI_ERROR_ARGUMENT negative (-6) . > Could someone help me? i'm reallygoing crazy. > > What i did is just a fire-before C trigger that acts like this: Is the trigger fired on INSERT, UPDATE, or both? > oldtuple = trigdata->tg_trigtuple; > newtuple = trigdata->tg_newtuple; [...] > rettuple=SPI_modifytuple(rel,newtuple,1,&attnum,&new_value,&isnull); I didn't look closely at the rest of the code, but if the trigger is fired on INSERT then you should pass oldtuple; newtuple will be NULL, causing SPI_modifytuple() to fail with SPI_ERROR_ARGUMENT. Here's an extract from "Writing Trigger Functions in C" in the "Triggers" chapter of the documentation: tg_trigtuple A pointer to the row for which the trigger was fired. This is the row being inserted, updated, or deleted. If this triggerwas fired for an INSERT or DELETE then this is what you should return from the function if you don't want to replacethe row with a different one (in the case of INSERT) or skip the operation. tg_newtuple A pointer to the new version of the row, if the trigger was fired for an UPDATE, and NULL if it is for an INSERT or a DELETE. This is what you have to return from the function if the event is an UPDATE and you don't want to replace this rowby a different one or skip the operation. -- Michael Fuhr
thanks a lot Michael, you've just hit the problem! Now everything works fine! Thanks again Fabrizio On 9/12/05, Michael Fuhr <mike@fuhr.org> wrote: > On Mon, Sep 12, 2005 at 04:17:33PM +0200, fabrizio picca wrote: > > I'm trying to sipmply modify a tuple before it will be inserted in the db. > > The problem is that when it try to insert the modified tuple with > > SPI_modifytuple all i get is a SPI_ERROR_ARGUMENT negative (-6) . > > Could someone help me? i'm reallygoing crazy. > > > > What i did is just a fire-before C trigger that acts like this: > > Is the trigger fired on INSERT, UPDATE, or both? > > > oldtuple = trigdata->tg_trigtuple; > > newtuple = trigdata->tg_newtuple; > [...] > > rettuple=SPI_modifytuple(rel,newtuple,1,&attnum,&new_value,&isnull); > > I didn't look closely at the rest of the code, but if the trigger > is fired on INSERT then you should pass oldtuple; newtuple will be > NULL, causing SPI_modifytuple() to fail with SPI_ERROR_ARGUMENT. > Here's an extract from "Writing Trigger Functions in C" in the > "Triggers" chapter of the documentation: > > tg_trigtuple > > A pointer to the row for which the trigger was fired. This is > the row being inserted, updated, or deleted. If this trigger was > fired for an INSERT or DELETE then this is what you should return > from the function if you don't want to replace the row with a > different one (in the case of INSERT) or skip the operation. > > tg_newtuple > > A pointer to the new version of the row, if the trigger was fired > for an UPDATE, and NULL if it is for an INSERT or a DELETE. This > is what you have to return from the function if the event is an > UPDATE and you don't want to replace this row by a different one > or skip the operation. > > -- > Michael Fuhr > -- L'Estetica del lavoro è lo spettacolo della merce umana (Area) -- http://www.fabpicca.net