Re: question
От | Sandeep Joshi |
---|---|
Тема | Re: question |
Дата | |
Msg-id | 3A3A9CC9.58644C3C@zambeel.com обсуждение исходный текст |
Ответ на | question (Sandeep Joshi <sjoshi@Zambeel.com>) |
Список | pgsql-general |
I have attached my program which I am trying to load as a trigger function. I was unsuccessful. I cannot link due to "elog" and "SPI_*" functions. which library are they defined in? I am linking "-lpq" on the command line. Sandeep > * Sandeep Joshi <sjoshi@Zambeel.com> [001215 12:43] wrote: > > > > I need to update a second database through a trigger function written > > 'C'. > > > > Is that possible ? how? > > > > Do I need to go to commercial product for this? > > No, just reading the documentation should help. :) > > I imagine you should be able to link to libpq and do the updates > using that. > > -- > -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] > "I have the heart of a child; I keep it in a jar on my desk." #include <stdio.h> #include "libpq-fe.h" #include "executor/spi.h" /* this is what you need to work with spi */ #include "commands/trigger.h" /* -"- and triggers */ HeapTuple insert_into_db2(void); HeapTuple insert_into_db2(void) { Trigger *trigger; /* to get trigger name */ int nargs,i,j; /* # of arguments */ Datum newval; /* new value of column */ char **args; /* arguments */ char *relname; /* triggered relation name */ Relation rel; /* triggered relation */ HeapTuple rettuple = NULL; TupleDesc tupdesc; /* tuple description */ int attnum,natts; Datum *cvals; /* column values */ char *cnulls; /* column nulls */ bool isnull; char *fieldval, *fieldtype; char *pghost, *pgport, *pgoptions, *pgtty, *dbName; PGconn *conn; PGresult *res; /* sanity checks from autoinc.c */ if (!CurrentTriggerData) elog(ERROR, "insert_username: triggers are not initialized"); if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData->tg_event)) elog(ERROR, "insert_username: can't process STATEMENT events"); if (TRIGGER_FIRED_AFTER(CurrentTriggerData->tg_event)) elog(ERROR, "insert_username: must be fired before event"); if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event)) rettuple = CurrentTriggerData->tg_trigtuple; else if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event)) rettuple = CurrentTriggerData->tg_newtuple; else elog(ERROR, "insert_username: can't process DELETE events"); rel = CurrentTriggerData->tg_relation; relname = SPI_getrelname(rel); trigger = CurrentTriggerData->tg_trigger; nargs = trigger->tgnargs; if (nargs != 3) elog(ERROR, "insert_username (%s): one argument was expected", relname); args = trigger->tgargs; tupdesc = rel->rd_att; natts = tupdesc->natts; CurrentTriggerData = NULL; attnum = SPI_fnumber(tupdesc, args[0]); /* Fetch tuple values and nulls cvals = (Datum *) palloc(natts * sizeof(Datum)); cnulls = (char *) palloc(natts * sizeof(char)); for (i = 0; i < natts; i++) { cvals[i] = SPI_getbinval(rettuple,tupdesc, i + 1, &isnull); cnulls[i] = (isnull) ? 'n' : ' '; } */ elog(NOTICE,"values %s. %s\n", SPI_fname(tupdesc, 1), SPI_getvalue(rettuple, tupdesc, 1)); elog(NOTICE,"values %s. %s\n", SPI_fname(tupdesc, 2), SPI_getvalue(rettuple, tupdesc, 2)); elog(NOTICE,"values %s. %s\n", SPI_fname(tupdesc, 3), SPI_getvalue(rettuple, tupdesc, 3)); pghost = NULL; /* host name of the backend server */ pgport = NULL; /* port of the backend server */ pgoptions = NULL; /* special options to start up the backend * server */ pgtty = NULL; /* debugging tty for the backend server */ dbName = "db2"; /* make a connection to the database */ conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName); /* check to see that the backend connection was successfully made */ if (PQstatus(conn) == CONNECTION_BAD) { elog(ERROR,"conneciton bad.\n"); } res = PQexec(conn, "BEGIN"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { elog(ERROR,"conneciton bad.\n"); PQclear(res); } PQclear(res); res = PQexec(conn, "insert into db2user values (3,'test123','n05aWAjCn2cqo'); "); if (PQresultStatus(res) != PGRES_COMMAND_OK) { elog(ERROR,"PQexec bad.\n"); } PQclear(res); res = PQexec(conn, "END"); PQclear(res); /* close the connection to the database and cleanup */ PQfinish(conn); pfree(relname); return (rettuple); }
В списке pgsql-general по дате отправления: