Re: Recursive stored procedure in C.

Leif Jensen <leif(at)crysberg(dot)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)
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.

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
via a SPI-executed query then you need to do it explicitly.

regards, tom lane