Thank you very much Joe.
I have followed the crosstab() implementation and understood the idea of
per query memory context. Now, I am using a unique SPI instance (which I
perform several sql queries), process the result, transform my result into
a tuplestore, close the SPI and done. It works perfectly.

Advertising

I have a curiosity with regard to the tuplestore: is there a problem with
performance if my tuplestore form a big table with million of tuples? Other
question is regarding to SPI: is there a problem to use only one instance
of SPI (for instance, if multiple users call the same function)?
Thank you again,
Anderson Carniel
2016-05-14 12:19 GMT-03:00 Joe Conway <m...@joeconway.com>:
> On 05/13/2016 09:35 PM, Anderson Carniel wrote:
> > I am writing a function that returns a set of tuples by using also the
> > PostGIS. Thuis, I am using SRF too. It successfully returns the expected
> > result when it has at most 4 tuples. However, this is not the case when
> > more than 4 tuples have to be returned. When I debug the code, I found
> > that the problem is in my function that transforms a cstring after a
> > SPI_connection. It seems that this cstring is not valid anymore in the
> > moment of this conversion (see my comment below). I know that the SPI
> > uses different contexts when it init and finish its process. But, I
> > don't understand why I have this problem here. Please, note that I tried
> > to copy the values of the whole tuple, but I have the same problem:
> > system crash after the forth call of the function. Also note that I call
> > this function only in the init call of the SRF. Please I would
> > appreciate any suggestion and help.
>
> You probably need to allocate your returned values in a per query memory
> context. Take a look at how it is done in, for example, crosstab() in
> contrib/tablefunc.
>
> HTH,
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>