Postgres Pro
Downloads
Home   >   mailing lists

Thread: modifying new tuple on insert in c trigger

modifying new tuple on insert in c trigger

From
"Damjan Pipan"
Date:
Hello!

I'm trying to write a c trigger function which would change an
attribute of tuple being inserted. I have read this value
from some other table and now I have a problem, because
even if I change the tuple with SPI_modifytuple and return
modified tuple it does not show in db (it did not change the tuple)
but tuple is inserted.

the main part of code

--------
 HeapTuple tmptuple;
 Datum new_value[2];
 int attnum[2];
 char r[3]="  ";

new_value[0] = SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1);
new_value[1] = SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1);
 attnum[0] = SPI_fnumber(tupdesc, fk_key_c);
 attnum[1] = SPI_fnumber(tupdesc, fk_key_c);
 tmptuple = SPI_modifytuple(rel, tuple, 1, attnum, new_value, r);
 if (tuple == NULL)
  elog(ERROR, "pltcl: SPI_modifytuple() failed - RC = %d\n", SPI_result);

  return PointerGetDatum(tmptuple);
----




here is the whole function:

-------
PG_FUNCTION_INFO_V1(ccc);

Datum
ccc(PG_FUNCTION_ARGS)
{
  TriggerData *trigdata = (TriggerData *) fcinfo->context;
  HeapTuple tuple;
  HeapTuple tmptuple;
  Relation rel;   /* triggered relation */
  TupleDesc tupdesc;  /* tuple description */
  char query[200];
  int ret;

  tuple = trigdata->tg_trigtuple;
  rel = trigdata->tg_relation;
  tupdesc = rel->rd_att;

  SPI_connect();


  sprintf(query, "select pk_table, pk_key_i, pk_key_c, fk_table, fk_key_i, fk_key_c FROM c00_doubles WHERE fk_table =
'%s';",
SPI_getrelname(trigdata->tg_relation));

  if ((ret = SPI_exec(query, 0)) < 0)
    elog(ERROR, "ccc (fired %s): SPI_exec returned %d", query, ret);

  if (SPI_processed > 0) {
    char pk_table[51];
    char pk_key_i[51];
    char pk_key_c[51];
    char fk_table[51];
    char fk_key_i[51];
    char fk_key_c[51];
    int key_i;
    char key_c[51];
    bool isnull;
    TUPLE  Tuple = (TUPLE) PG_GETARG_POINTER(0);


    strcpy(pk_table, DatumGetCString(SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1)));
    strcpy(pk_key_i, DatumGetCString(SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 2)));
    strcpy(pk_key_c, DatumGetCString(SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 3)));
    strcpy(fk_table, DatumGetCString(SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 4)));
    strcpy(fk_key_i, DatumGetCString(SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 5)));
    strcpy(fk_key_c, DatumGetCString(SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 6)));


    {
      bool isnull;
      key_i = DatumGetInt32(SPI_getbinval(tuple, tupdesc, SPI_fnumber(tupdesc, fk_key_i), &isnull));

      if (isnull)
 elog(ERROR, "NULLLLL");
    }

    sprintf(query, "SELECT %s FROM %s WHERE %s = %d", pk_key_c, pk_table, pk_key_i, key_i);

    if ((ret = SPI_exec(query, 0)) < 0)
      elog(ERROR, "ccc (fired %s): SPI_exec returned %d", query, ret);

    if (SPI_processed > 0) {
      strcpy(key_c, DatumGetCString(SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1)));
      {
 // HeapTuple tmptuple;
 Datum new_value[2];
 int attnum[2];
 char r[3]="  ";

 new_value[0] = SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1);//CStringGetDatum(key_c);
 new_value[1] = SPI_getvalue(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1);//CStringGetDatum(key_c);
 attnum[0] = SPI_fnumber(tupdesc, fk_key_c);
 attnum[1] = SPI_fnumber(tupdesc, fk_key_c);
 // elog (ERROR, "'%s'", key_c);
 // tmptuple = SPI_copytuple(tuple);
 tmptuple = SPI_modifytuple(rel, tuple, 1, attnum, new_value, r);
 // SPI_freetuple(tmptuple);
 if (tuple == NULL)
  elog(ERROR, "pltcl: SPI_modifytuple() failed - RC = %d\n", SPI_result);

      }
    }
    SPI_finish();    /* don't forget say Bye to SPI mgr */

  }
  //  PG_RETURN_BOOL(salary > 699);
  return PointerGetDatum(tmptuple);
}

Re: modifying new tuple on insert in c trigger

From
Tom Lane
Date:
"Damjan Pipan" <damjan.pipan@xlab.si> writes:
> I'm trying to write a c trigger function which would change an
> attribute of tuple being inserted. I have read this value
> from some other table and now I have a problem, because
> even if I change the tuple with SPI_modifytuple and return
> modified tuple it does not show in db (it did not change the tuple)
> but tuple is inserted.

Before groveling through the C code ... did you declare the trigger
as BEFORE or AFTER?  Only BEFORE triggers get to change the inserted
tuple.

Also, I think the "r" array needs to contain 'r' not ' ' at the
positions where you want to replace columns.

            regards, tom lane

Re: modifying new tuple on insert in c trigger

From
"Damjan Pipan"
Date:
Thanks for the help, but I have solved the problem already.

The problem was that I tried to do
char *a = DatumGetCString(SPI_getbinval(...)) for a varchar field
and it did not work. I'm using now SPI_getvalue

Damjan

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Damjan Pipan" <damjan.pipan@xlab.si>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, November 27, 2002 4:57 AM
Subject: Re: [GENERAL] modifying new tuple on insert in c trigger


> "Damjan Pipan" <damjan.pipan@xlab.si> writes:
> > I'm trying to write a c trigger function which would change an
> > attribute of tuple being inserted. I have read this value
> > from some other table and now I have a problem, because
> > even if I change the tuple with SPI_modifytuple and return
> > modified tuple it does not show in db (it did not change the tuple)
> > but tuple is inserted.
>
> Before groveling through the C code ... did you declare the trigger
> as BEFORE or AFTER?  Only BEFORE triggers get to change the inserted
> tuple.
>
> Also, I think the "r" array needs to contain 'r' not ' ' at the
> positions where you want to replace columns.
>
> regards, tom lane
>
>