[HACKERS] creating a complex aggregate function

[Najib Abi Fadel]

Hi i have an ordered table of dates let's say:   1/1/2004 8/1/2004 15/1/2004 29/1/2004 5/2/2004 12/2/2004   I am searching for a way to have the minimum date and maximum date for dates seperated by one week whitout gaps between them in a string. which will give the following output: 1/1/2004:15/1/2004;29/1/2004:12/2/2004;   I was thinking of doing this with an aggregate function.   So i thought about writing the following C code :   #include "postgres.h" #include "utils/date.h"   int32 i;//initially equal to zero text * charresult; /* text * concat(,){} // NOT IMPLEMENTED (HOW TO DO IT ??) */   PG_FUNCTION_INFO_V1(computechar);   Datum  computechar(PG_FUNCTION_ARGS) {              DateADT d1=PG_GETARG_DATEADT(0);            DateADT d2=PG_GETARG_DATEADT(1);               int32 diff= (int32) (d2 - d1);             i++;                 if(diff == 7*i)                 PG_RETURN_DATEADT(d1);               else                 {                charresult=concat(charresult,d1);                charresult=concat(charresult,":");                charresult=concat(charresult,d2);                charresult=concat(charresult,";");                PG_RETURN_DATEADT(d2);                  } }   PG_FUNCTION_INFO_V1(returncomputedchar);       Datum returncomputedchar (PG_FUNCTION_ARGS) {                                                                                    PG_RETURN_TEXT_P(charresult);                                         }   And then i will create the aggregate as follows (after compiling ...) :   CREATE OR REPLACE FUNCTION computechar(date,date) returns date as '/home/nabifadel/tempo/groupingWeeks.so' LANGUAGE 'C' WITH (isStrict); CREATE OR REPLACE FUNCTION returncomputedchar (date) returns text as '/home/nabifadel/tempo/groupingWeeks.so' LANGUAGE 'C' WITH (isStrict);   CREATE AGGREGATE groupe_weeks_agg( basetype = date, sfunc = computechar,stype = date, finalfunc = returncomputedchar);     The function 'computechar' will put the result in the variable 'charresult' that will be returned by the function 'returncomputedchar'.   My first question is : Could this work the way i am thinking of it ??   My second question is how to implement the function 'concat' that will return a text concatenation of  dates and text. (the question is basically how to transform a date into text).   It's the first time i try to implement an aggregate function and i need help + i haven't been using C language frequently the last few years (there may be errors) .   Thx.