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.

[Najib Abi Fadel]

I forgot to mention that in fact i will have an ordered table of dates ( i mean by ordered i will do an order by before applying the aggregate) for different users.
That's why i need an aggregate ( i have to do a group by)

so i have something like this
01/01/2004 user1
08/01/2004 user1
15/01/2004 user1
29/01/2004 user1
05/02/2004 user1
12/02/2004 user1

25/12/2003 user2
01/01/2004 user2
15/01/2004 user2
22/01/2004 user2
29/01/2004 user2
05/02/2004 user2
12/02/2004 user2

which should produce the output:

01/01/2004:15/01/2004;29/01/2004:12/02/2004; user1
25/12/2003:01/01/2004;15/01/2004:12/02/2004; user2

I hope someone will help me in this issue.
----- Original Message -----
From: Najib Abi Fadel
To: generalpost
Cc: developPost
Sent: Monday, July 05, 2004 12:23 PM
Subject: [GENERAL] creating a complex aggregate function
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.