Thread: Insert into other database

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??

Thanks in advance,

Rodolfo Campos.

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.4 that'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.