Re: question

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);

}