modifying new tuple on insert in c trigger - Mailing list pgsql-general

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