Re: Nested SPI_exec's

Hi Tom,

Thanks for the fast response. One learns something new everyday!

Cheers,

Jason

On Tue, 7 Oct 2003 12:14 am, Tom Lane wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly