From: | Javier Reyes <c(dot)javier(dot)reyes(dot)e(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Equivalent plpgsql trigger in C |
Date: | 2012-02-03 10:50:39 |
Message-ID: | CA+pgUQTpzP+dWLFKbSOH6xFe+iehtN4Sqfb+G9j4HVLQFZ4DQQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | John Morgan | 2012-02-03 12:45:37 | case statement to cath nulls on joined tables |
Previous Message | girish R G peetle | 2012-02-03 10:36:42 | Postges 9.0 WAL SEGEMENT SIZE is 128 GB for default server configuration. |