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 > >
