Equivalent plpgsql trigger in C

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux