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
Doug McNaught
Date:
"Damjan Pipan" <damjan.pipan@siol.net> writes:

> 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.

Is it a BEFORE or AFTER trigger?  If it's modifying the inserted tuple
it needs to be BEFORE.

-Doug

Re: modifying new tuple on insert in c trigger

From
"Damjan Pipan"
Date:
I have already found the error - the functions CStringGetDatum and SPI_getvalue
does not work as I have expected. Now I'm getting values in binary and assigning
them directly to new value.

BTW

How can you transform a value in char* to Datum or vice versa? I tried with
CStringGetDatum and DatumGetCString but it did not work.

Damjan

----- Original Message -----
From: "Doug McNaught" <doug@mcnaught.org>
To: "Damjan Pipan" <damjan.pipan@siol.net>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, November 20, 2002 2:45 PM
Subject: Re: [GENERAL] modifying new tuple on insert in c trigger


> "Damjan Pipan" <damjan.pipan@siol.net> writes:
>
> > 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.
>
> Is it a BEFORE or AFTER trigger?  If it's modifying the inserted tuple
> it needs to be BEFORE.
>
> -Doug
>