[ADMIN] Equivalent plpgsql trigger in C

[Javier Reyes]

Hi.

I've a PostgreSQL 9.0 server and I'm using heritage on some tables, for
this reason I have to simulate foreign keys through triggers like this:

*CREATE OR REPLACE FUNCTION othertable_before_update_trigger()
RETURNS trigger AS
$BODY$
DECLARE
  sql   VARCHAR;
  rows  SMALLINT;
BEGIN
  IF (NEW.parenttable_id IS DISTINCT FROM OLD.parenttable_id) THEN
    sql  := 'SELECT id '
         || 'FROM parentTable '
         || 'WHERE id = ' || NEW.parenttable_id || ';';
     BEGIN
        EXECUTE sql;
        GET DIAGNOSTICS rows = ROW_COUNT;

     EXCEPTION
        WHEN OTHERS THEN
           RAISE EXCEPTION 'Error when I try find in parentTable the
id %. SQL: %. ERROR: %',
                            NEW.parenttable_id,sql,SQLERRM;
     END;

     IF rows = 0 THEN
        RAISE EXCEPTION 'Not found a row in parentTable with id %.
SQL: %.',NEW.parenttable_id,sql;
     END IF;
  END IF;
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;*


But due to performance I try to create a equivalent trigger in C code:

*#include "postgres.h"
#include "executor/spi.h"       /* this is what you need to work with SPI */
#include "commands/trigger.h"   /* ... and triggers */

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
extern Datum othertable_before_update_trigger(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(othertable_before_update_trigger);

Datum
 othertable_before_update_trigger(PG_FUNCTION_ARGS) {

   TriggerData *trigdata = (TriggerData *) fcinfo->context;
   TupleDesc   tupdesc;
   HeapTuple   rettuple;
   bool        isnull;
   int         ret, i;

   /* make sure it's called as a trigger at all */
   if (!CALLED_AS_TRIGGER(fcinfo))
     elog(ERROR, "othertable_before_update_trigger: not called by
trigger manager");

   /* tuple to return to executor */
   if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
     rettuple = trigdata->tg_newtuple;
   else
     rettuple = trigdata->tg_trigtuple;

   tupdesc = trigdata->tg_relation->rd_att;

   /* connect to SPI manager */
   if ((ret = SPI_connect()) < 0)
     elog(ERROR, "othertable_before_update_trigger (fired %s):
SPI_connect returned %d", "before", ret);

   [A]

   [B]

   return PointerGetDatum(rettuple);
 }*


I need fill the code in:

   1.

   *[A]*: get the previous and new values for *parenttable_id*. With:

   *int32 att = DatumGetInt32(heap_getattr(rettuple, 1, tupdesc, &isnull));*

   or

   *int32 att = DatumGetInt32(SPI_getbinval(rettuple, tupdesc, 1, &isnull));
   *

I can get only the old value of *parenttable_id* but not the new value.
Even if I try to use the column name instead of their number with:

*GetAttributeByName (rettuple->t_data, "parenttable_id", &isnull);*

Getting error: *record type has not been registered*

   1. *[B]*: execute the query *SELECT id FROM parentTable WHERE id =
   NEW.parenttable_id*

I found the function *SPI_execute_with_args*, but I haven't found examples
of this for my case.

Thanks in advance.