Re: Insert into other database

Fellows,

I've restarted postgres after installing postgresql-dev package on my sarge
debian box and that's it, I can connect to other database using libpq. Here
is my code for anyone interested in this issue. I compiled the source code
using:

gcc -fpic -shared -lpq -I/usr/include/postgresql/server/
-I/usr/include/postgresql/ completo.c -o completo.so

The code (completo.c):

/*Este tiene conexion a otras BD*/
#include "postgres.h"
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* ... and triggers */
#include "libpq-fe.h"

#define TAM_QUERY 300
#define TAM_VALORES 200

extern Datum completo(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(completo);

void
imprimeInfoColumna(HeapTuple rettuple, TupleDesc tupdesc, int i)
{
elog(NOTICE, "La columna %s (%d): %s", SPI_fname(tupdesc, i), i,
SPI_getvalue(rettuple, tupdesc, i));
}

HeapTuple
ejecutaReglas(TriggerData *trigdata)
{
TupleDesc tupdesc = trigdata->tg_relation->rd_att;
HeapTuple rettuple;
HeapTupleHeader heapTupleHeader;
char query[TAM_QUERY], valores[TAM_VALORES];
int ret, i, numAtts;
PGconn *conn;
PGresult *res;
const char *conninfo = "dbname=otra user=admin password=123456";

/* tuple to return to executor */
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) {
elog(INFO, "Disparado por UPDATE");

rettuple = trigdata->tg_newtuple; //Fila que dispara el trigger
heapTupleHeader = rettuple->t_data; //Cabecera de la fila INFO
numAtts = heapTupleHeader->t_natts; //Numero de atributos en fila

//Recorriendo fila para extraer valores
for (i=1;i<=numAtts;i++) {
imprimeInfoColumna(rettuple, tupdesc, i);
}
}
else if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) {
elog(INFO, "Disparado por INSERT..");

rettuple = trigdata->tg_trigtuple; //Fila que dispara el trigger
heapTupleHeader = rettuple->t_data; //Cabecera de la fila INFO
numAtts = heapTupleHeader->t_natts; //Numero de atributos en fila

//Inicializando cadenas para la construccion del query
strncpy(query, "INSERT INTO prueba2 VALUES", TAM_QUERY);
strncpy(valores, "(", TAM_VALORES);

//Recorriendo fila para extraer valores
for (i=1;i<=numAtts;i++) {
imprimeInfoColumna(rettuple, tupdesc, i);

if (strstr(SPI_gettype(tupdesc, i), "text") != NULL) {
//Cuando el campo es de tipo text
strcat(valores, "'");
strcat(valores, SPI_getvalue(rettuple, tupdesc, i));
strcat(valores, "'");
}
else {
//Cuando el campo no es de tipo text
strcat(valores, SPI_getvalue(rettuple, tupdesc, i));
}

if (i<numAtts)
strcat(valores, ", "); //En caso de que falten
argumentos
else
strcat(valores, ")"); //Cuando no existan mas
argumentos
}

//Armando el query final
strcat(query, valores);
elog(NOTICE, "%s", query);

/* Make a connection to the database */
conn = PQconnectdb(conninfo);
res = PQexec(conn, query);
if (PQresultStatus(res) == PGRES_COMMAND_OK)
{
elog(NOTICE, "insercion en la otra bd fina...");
}
PQfinish(conn);

}
else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)) {
elog(INFO, "Disparado por DELETE");

rettuple = trigdata->tg_trigtuple;

}
else {
elog(ERROR, "Disparado por ERROR");
rettuple = NULL;

}

return rettuple;
}

Datum
completo(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
HeapTuple rettuple;

/* make sure it's called as a trigger at all */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "trigf: not called by trigger manager");
else {
rettuple = ejecutaReglas(trigdata);
}

return PointerGetDatum(rettuple);
}

Thank you very much for your unvaluable help,

Rodolfo.

On 2/8/06, Rodolfo Campos <camposer(at)gmail(dot)com> wrote:
>
> Thanks Michael,
>
> I'll check it out. I forgot to say to you that I'm using postgresql 7.4that's why I can't use pl/perl.
>
> Greetings,
>
> Rodolfo.
>
>
> On 2/8/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
> >
> > [Please copy the mailing list on replies.]
> >
> > On Wed, Feb 08, 2006 at 06:57:11PM -0400, Rodolfo Campos wrote:
> > > When I tried to connect using libpq I got errors too. But this time
> > the
> > > error is trying to register the function in postgresql, the RDBMS
> > tells me
> > > that the function PQconnectdb is undefined.
> > >
> > > I don't obtain errors compiling the code. The command that I used was:
> > >
> > > gcc -fpic -shared -I/usr/include/postgresql
> > -I/usr/include/postgresql/server
> > > -lpq test.c -o test.so
> >
> > What does "ldd ./test.so" show? You might need to specify additional
> > flags to give hints about libpq's location to the runtime linker;
> > another possibility would be to set an environment variable like
> > LD_LIBRARY_PATH.
> >
> > Have a look at contrib/dblink/Makefile and "Extension Building
> > Infrastructure" in the documentation (8.0 and later). It's usually
> > easiest to let PostgreSQL figure out how to build extensions.
> >
> > http://www.postgresql.org/docs/8.1/interactive/xfunc-c.html#XFUNC-C-PGXS
> >
> > > Another question, Can I make triggers using Perl, because I read a
> > paper
> > > where they say that we can't.
> >
> > PL/Perl triggers are supported in 8.0 and later; see the documentation
> > for the version you're running.
> >
> > http://www.postgresql.org/docs/8.1/interactive/plperl-triggers.html
> >
> > --
> > Michael Fuhr
> >
>
>