Custom aggragation function that creates an array
Hi,
I want to write a custom agg function that, given an "int4 index",
increments the element at "index" of an array and, at the end, returns
the array. The array will always be int4[].
I need it in C, since plpgsql is way slower (and I need to use it in
5M+ rows). I did it, but I also need to create the array "on demand",
not as initcond (the array size will be a parameter).
I suppose that, since I'll be using "construct_array" (if it makes
sense), I'll need a new MemoryContext?
I've looked at array_agg_transfn, but I think that's a lot more
complicated because it'll repalloc, so it has to keep
the memorycontext...
Since I'm not familiar with these things, would something like this
work?
if (PG_ARGISNULL(0))
{ // first time, alloc array
arr_context = AllocSetContextCreate(rcontext,
"myCustomArrayResult",
ALLOCSET_DEFAULT_MINSIZE,
ALLOCSET_DEFAULT_INITSIZE,
ALLOCSET_DEFAULT_MAXSIZE);
oldcontext = MemoryContextSwitchTo(arr_context);
transarray = construct_array([...]);
MemoryContextSwitchTo(oldcontext );
}
else
{
transarray = PG_GETARG_ARRAYTYPE_P(0);
}
This is how the C function is written right now:
ArrayType *transarray;
int32 index = PG_GETARG_INT32(1);
int32 *array;
if (AggCheckCallContext(fcinfo, NULL))
{
// code above would go in place of this line:
transarray = PG_GETARG_ARRAYTYPE_P(0);
}
else
transarray = PG_GETARG_ARRAYTYPE_P_COPY(0);
array = (int32*)ARR_DATA_PTR(transarray);
array[index]++;
PG_RETURN_ARRAYTYPE_P(transarray);
(checks on boundaries etc omitted)
The problem is that it expects the array to be already available, so I had to
do:
CREATE AGGREGATE cust_agg_func (int4)
(
sfunc = cust_agg_funct_add,
stype = int4[],
initcond = '{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}'
);
I would like to have:
CREATE AGGREGATE cust_agg_func (int4 index, int4
array_size_to_be_created)
(
sfunc = cust_agg_funct_with_array_creation_add,
stype = int4[]
);
On Fri, Oct 22, 2010 at 02:02:48PM +0100, Leonardo Francalanci wrote:
Hi,
I want to write a custom agg function that, given an "int4 index",
increments the element at "index" of an array and, at the end,
returns the array. The array will always be int4[].
Before you start coding, have you looked over what's in
contrib/intarray ?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Before you start coding, have you looked over what's in
contrib/intarray ?
There's nothing that fulfills my needs there, but I guess it
would be the perfect place to watch for code examples!
Thank you
(I think I made my custom aggregation function work,
but I'll look into intarray code to see if I made mistakes...)