Re: Insert into other database - Mailing list pgsql-general

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.