Server allocates memory in memory contexts in such way that allocations made in one context may be freed by context destruction without affecting allocations made in other contexts. All allocations (via palloc, etc) are made in the context that is chosen as the current one. You'll get unpredictable results if you'll try to free (or reallocate) memory allocated not in current context.
Creation and switching between memory contexts are subject of SPI manager memory management.
SPI procedures deal with two memory contexts: upper Executor memory context and procedure memory context (if connected).
Before a procedure is connected to the SPI manager, current memory context is upper Executor context so all allocation made by the procedure itself via palloc/repalloc or by SPI utility functions before connecting to SPI are made in this context.
After SPI_connect is called current context is the procedure's one. All allocations made via palloc/repalloc or by SPI utility functions (except for SPI_copytuple, SPI_modifytuple, SPI_palloc and SPI_repalloc) are made in this context.
When a procedure disconnects from the SPI manager (via SPI_finish) the current context is restored to the upper Executor context and all allocations made in the procedure memory context are freed and can't be used any more!
If you want to return something to the upper Executor then you have to allocate memory for this in the upper context!
SPI has no ability to automatically free allocations in the upper Executor context!
SPI automatically frees memory allocated during execution of a query when this query is done!