Tom Lane wrote:
>
> "Stephen P. Berry" <spb@meshuggeneh.net> writes:
> > Is there any way to keep `intermediate' data used by user-defined
> > functions around indefinitely? I.e., have some sort of crunch_init()
> > function that creates a bunch of in-memory data structures, which
> > can then be used by subsequent (and independent) queries?
I have had to deal with this problem. I implemented a small version of Oracle's
"contains()" API call. The API is something like this:
select score(1), score(2) from table where contains(cola, 'bla bla bal', 1) >0
and contains(colb, 'fubar', 2) > 1;
On the first call I parse the search string and store it in a hash table based
on the number passed to both contains() and score(). The number passed is an
arbitrary bookmark which separates the various result sets for a single query.
The hash table is static data allocated with malloc(), although I have been
thinking I should use MemoryContextAlloc with the right context, but malloc
seems to work.
On subsequent queries, if the bookmark numer is is found, but the string for
the contains function differes, then I delete the old entry and reparse and
store the new one.
>
> > It seems like the general class of thing I'm trying to accomplish
> > isn't that esoteric. Imagine trying to write a function to compute
> > the standard deviation of arbitrary precision numbers using the GMP
> > library or some such. Note that I'm not saying that that's what I'm
> > trying to do...I'm just offering it as a simple sample problem in
> > which one can't pass everything as an argument in an aggregate. How
> > does one set about doing such a thing in Postgres?
>
> I blink not an eye to say that I'd do it exactly as described above.
> Stick all the intermediate state into a data structure that's referenced
> by a single master pointer, and pass the pointer as the "state value"
> of the aggregate.
>
> BTW, mlw posted some contrib code on pghackers just a day or two back
> that does something similar to this. He did some details differently
> than I would've, notably this INT32-vs-POINTER business; but it's a
> working example.
The sizeof(int32) == sizeof(void *) is a problem, and I am not happy with it,
although I will look into your (Tom) recommendations.
