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@xxxxxxxxx> wrote:
Thanks Michael,
I'll check it out. I forgot to say to you that I'm using postgresql 7.4 that's why I can't use pl/perl.
Greetings,
Rodolfo.
On 2/8/06, Michael Fuhr < mike@xxxxxxxx> 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
