Re: Recursive stored procedure in C.

   Hi Tom,

  Thank you for the suggestions. I didn't know anything about SPI_push and
SPI_pop and I will walk through my code and stuff them in. Am I looking at
some wrong documentation ? I never saw anything about those ?

  Do you know any good examples doing such things ?

 Leif


On Thu, 14 Jul 2005, Tom Lane wrote:

> Leif Jensen <leif@crysberg.dk> writes:
> >    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.
>
> If you want the trigger levels to be independent (which I think you do)

   Yes.

> then you need a SPI_connect and a SPI_finish in *each* trigger call.
> Whatever magic you are doing to special-case recursion is all wrong and
> should be ripped out, root and branch.

   Ok, will do.

>
> The bit you may be missing is that you need to do SPI_push and SPI_pop
> around anything that might possibly call another function using SPI (eg,
> your own recursive instance, but also anything else that might use SPI).
> SPI_execute() and friends do this for you, but if the recursion is not

   SPI_exec() ?

> via a SPI-executed query then you need to do it explicitly.
>
>             regards, tom lane
>