Error loading trigger in C

[Juan Jose Costello Levien]

Hello,
I am trying to use a trigger function I wrote in C. Basically what I
want to do is to audit a table when a row is inserted into another table
by copying the row to the new table. It compiles Ok and I created a
shared library trigger.so. But when I load it into pgAdmin it tells me
'An error had occured'.

To address that I put here the source code so you can check it out:
(note: it has a oid (blob) field the row, and that could be a problem
for the query syntax, maybe there is the error)

#include "postgres.h"
#include "executor/spi.h" /* SPI */
#include "commands/trigger.h" /* triggers */
#include <stdlib.h>
#include <string.h>
extern Datum trigf(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(trigf);
Datum
trigf(PG_FUNCTION_ARGS)
{

char * query = (char *)malloc(sizeof(char *)*500);

TriggerData *trigdata = (TriggerData *) fcinfo->context;

TupleDesc tupdesc;

HeapTuple rettuple;

char *when;

bool checknull = false;

bool isnull;

int ret, i;
/* aquí nos aseguramos que se llama a la función como un trigger*/

if (!CALLED_AS_TRIGGER(fcinfo))

elog(ERROR, "trigf: no se llamó por el administrador de triggers");
/* datos que devuelve el ejecutor */

if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))

rettuple = trigdata->tg_newtuple;

else

rettuple = trigdata->tg_trigtuple;
/* chequeo de valores nulos */

if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)

&& TRIGGER_FIRED_BEFORE(trigdata->tg_event))

checknull = true;
if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))

when = "antes";

else

when = "después";
tupdesc = trigdata->tg_relation->rd_att;
/* conexión al SPI (Server Programming Interface) */

if ((ret = SPI_connect()) < 0)

elog(INFO, "trigf (disparado %s): SPI_connect devolvió %d", when, ret);
/* ejecuto el query para insertar en la tabla de auditoría */

strcpy(query, "INSERT INTO visita_log VALUES ('");

strcat(query, trigdata->tg_trigger->tgargs[0]);

strcat(query, "','");

strcat(query, trigdata->tg_trigger->tgargs[1]);

strcat(query, "','");

strcat(query, trigdata->tg_trigger->tgargs[2]);

strcat(query, "','");

strcat(query, trigdata->tg_trigger->tgargs[3]);

strcat(query, "',");

strcat(query, trigdata->tg_trigger->tgargs[4]);

strcat(query, ",'");

strcat(query, trigdata->tg_trigger->tgargs[5]);

strcat(query, "');");
SPI_exec(query, 0);
/* count(*) devuelve int8, cuidado con la conversión */

i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
SPI_tuptable->tupdesc,

1,

&isnull));
elog (INFO, "trigf (disparado %s): hay %d filas en la tabla", when, i);
SPI_finish();
if (checknull)

{

SPI_getbinval(rettuple, tupdesc, 1, &isnull);

if (isnull)
rettuple = NULL;

}
return PointerGetDatum(rettuple);

}
// End of source code
I use PostgreSQL 7.4.1 and Mandrake 10.
The compiler sequence I used is:

gcc -I "./" -fpic -c trigger.c
gcc -I "./" -shared -o trigger.so trigger.o

and when I include it in Trigger functions pgAdmin complains with "An
error had occured" (and says nothing). What I am doing wrong?

Thanks in advance.
Juan

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Doug McNaught]

Juan Jose Costello Levien <jc*******@datafull.com> writes:

Hello,
I am trying to use a trigger function I wrote in C. Basically what I
want to do is to audit a table when a row is inserted into another
table by copying the row to the new table. It compiles Ok and I
created a shared library trigger.so. But when I load it into pgAdmin
it tells me 'An error had occured'.

Does it work if you take pgAdmin out of the loop? E.g. in psql:

CREATE FUNCTION trigf ...
CREATE TRIGGER ...

-Doug

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Juan Jose Costello Levien]

Doug,

You were right, psql let me insert the trigger.
But when I insert a new row in the table 'visita' (the one that has the
trigger), the result must be an audit new row on 'visita_log' because of
the trigger, and pgAccess reports that the connection was lost. Maybe is
something wrong with the trigger... I don't know what it is... (this is
the first time I program a trigger with PgSQL...)

Thanks in advance.

Juan
Doug McNaught wrote:

Juan Jose Costello Levien <jc*******@datafull.com> writes:

Hello,
I am trying to use a trigger function I wrote in C. Basically what I
want to do is to audit a table when a row is inserted into another
table by copying the row to the new table. It compiles Ok and I
created a shared library trigger.so. But when I load it into pgAdmin
it tells me 'An error had occured'.

Does it work if you take pgAdmin out of the loop? E.g. in psql:

CREATE FUNCTION trigf ...
CREATE TRIGGER ...

-Doug

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings