Re: Nested SPI_exec's

Jason Godden <jasongodden(at)optushome(dot)com(dot)au> writes:
> I'm in the process of moving a rather complicated plpgsql stored procedure to
> a C module and I'm curious about how to implement the for each row syntax in
> C. My understanding is that if you perform an SPI_exec whilst looping
> through the results of another exec the allocated SPITupleTable will be
> automatically unallocated. Is this correct or am I way off here and it's
> actually safe to iterate through the tuples in an SPITupleTable and perform
> additional SPI_exec's?

SPI won't auto-deallocate tuple tables unless you leave the procedure
(ie call SPI_finish). It's true that the static variable SPI_tuptable
will be overwritten by each exec, but you can just copy that into a
local variable and continue to operate on the tuple table till you
are done with it. It'd probably be wise to explicitly do
SPI_freetuptable when you are done with a result, if you're making
more of them inside a loop...

> The other query I have relates to the transaction 'safe-ness' of a C module.
> If I initiate all of this within a transaction (from outside the C module),
> is everything within the SPI module automagically encapsulated with the
> transaction or do I have to manage the transaction from within the SPI
> module?

You don't and in fact can't manage transactions in a callable procedure.

regards, tom lane