pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp needed on SPI_modifytuple.
3+ messages / 2 participants
[nested] [flat]
* help needed on SPI_modifytuple.
@ 2005-09-12 14:17 fabrizio picca <fabrizio.picca@gmail.com>
2005-09-12 15:36 ` Re: help needed on SPI_modifytuple. Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 3+ messages in thread
From: fabrizio picca @ 2005-09-12 14:17 UTC (permalink / raw)
To: pgsql-interfaces
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,"attnum NULL");
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: help needed on SPI_modifytuple.
2005-09-12 14:17 help needed on SPI_modifytuple. fabrizio picca <fabrizio.picca@gmail.com>
@ 2005-09-12 15:36 ` Michael Fuhr <mike@fuhr.org>
2005-09-12 16:01 ` Re: help needed on SPI_modifytuple. fabrizio picca <fabrizio.picca@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Fuhr @ 2005-09-12 15:36 UTC (permalink / raw)
To: fabrizio picca <fabrizio.picca@gmail.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: help needed on SPI_modifytuple.
2005-09-12 14:17 help needed on SPI_modifytuple. fabrizio picca <fabrizio.picca@gmail.com>
2005-09-12 15:36 ` Re: help needed on SPI_modifytuple. Michael Fuhr <mike@fuhr.org>
@ 2005-09-12 16:01 ` fabrizio picca <fabrizio.picca@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: fabrizio picca @ 2005-09-12 16:01 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-09-12 16:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-12 14:17 help needed on SPI_modifytuple. fabrizio picca <fabrizio.picca@gmail.com>
2005-09-12 15:36 ` Michael Fuhr <mike@fuhr.org>
2005-09-12 16:01 ` fabrizio picca <fabrizio.picca@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox