Insert into other database

On Wed, Feb 08, 2006 at 05:59:19PM -0400, Rodolfo Campos wrote:

I'm wondering if in postgres we can insert a tuple into a table that is
outside of a database (I'm supossed to be connected to database1 and want to
insert a register into a table in database2).

You can do this with contrib/dblink; search the list archives for
other possibilities (PL/Perl + DBI + DBD::Pg, dbi-link, etc.).

This question is because I want to update a tables into one database from a
trigger (written in C), and the table who fires the trigger is in another
database.

A C function can use libpq to connect to another database; that's
how dblink works. But such a design is often a hint to consider
using separate schemas in the same database rather than separate
databases. A problem with actions that take place outside the
current database is that you lose transactional control: if the
triggering transaction rolls back then you have no way to roll back
the outside changes.

--
Michael Fuhr

Hi Fellows,

I'm wondering if in postgres we can insert a tuple into a table that

is

outside of a database (I'm supossed to be connected to database1 and

want

to insert a register into a table in database2).

This question is because I want to update a tables into one database

from

a trigger (written in C), and the table who fires the trigger is in
another database.

I'm making the connection with SPI_connect. What can I do?? Is this
posible??

I'd like to be able to do this too, but when I asked the question it was
suggested that if you want to do this then you should use different
schema's within the same database, instead of different databases. Maybe
it's in the FAQ too?

Given that your trigger is written in C, couldn't you use libpq to
connect to and update the other database?

James

[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

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.

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.