Thread: Error loading trigger in C

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

Juan Jose Costello Levien <jcostello@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

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 <jcostello@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
>
>
>
>