Recursive stored procedure in C.

[Leif Jensen <leif@xxxxxxxxxxx>]

    Hi all,

   I am trying to make a stored procedure in C that is used as a trigger
on before/after insert/update on a certain table. This procedure might do
inserts/updates on the same table (recursively triggering itself). I have
made (pretty) sure that I'm not using 'global' variables in this module
and that I do an SPI_connect() the very first time I enter and an
SPI_finish() at the last exit.

   This works to some point, but in some instances I get
SPI_ERROR_UNCONNECTED from an SPI_exec() call a few recursive layers down,
e.g. the following code yields:

  ret = SPI_exec( sql_query, 0 );      => SPI_ERROR_UNCONNECTED
  if( ret == SPI_ERROR_UNCONNECTED ) {
    ret = SPI_finish();                => SPI_ERROR_UNCONNECTED
    ret = SPI_connect();               => SPI_ERROR_CONNECT
    ret = SPI_exec( sql_query, 0 );    => SPI_ERROR_UNCONNECTED
  }

  ??? (This 'if' was mainly to try to figure out what was going on, since
I should not do an SPI_finish() at all until the very end.)

   Is such recursivity at all possible ? What are the pitfalls ? Any good
examples out there?

   Any suggestions at all will be much appreciated,

 Leif

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend