I want to process all the records in a table through a C-language (well,
C++) function (i.e. one function call per row of the table) in such a way
that the function hangs onto its internal state across calls. Something
like
SELECT my_function(a, b, c) FROM my_table ORDER BY d;
The value returned in the last row of the table would be the result I'm
looking for. (This could be neatened up by using a custom aggregate and
putting my calculation in the sfunc but that's a minor detail).
The question is: what's the "best practice" way of letting a
C/C++-language function hang onto internal state across calls? So far I'm
thinking something along the lines of:
Datum my_function(int a, int b, int c, int reset)
{
static my_data *p = NULL;
if (reset) //(re)initialise internal state
{
delete p;
p = NULL;
}
else
{
if (!p)
{
p = new my_data;
}
//make use of internal state to do calculations or whatever
}
}
The user would be responsible for calling my_function with "reset" set to
true to wipe previous internal state before using the function in a new
query; doing this also frees the memory associated with the function.
This system is of course prone to leakage if the user forgets to wipe the
internal state after use, but it will only leak sizeof(my_data) per
connection, and the OS will garbage-collect all that when the connection
dies anyway.
Alternatively, use this in a custom aggregate and make the ffunc do the
garbage collection, which should prevents leakage altogether.
Is this a reasonable thing to do? What are the risks? Is there a more
"best-practice" way to achieve the same result?
Many thanks,
Matt
