From: | "Najib Abi Fadel" <nabifadel(at)usj(dot)edu(dot)lb> |
---|---|
To: | "generalpost" <pgsql-general(at)postgresql(dot)org> |
Cc: | "developPost" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | creating a complex aggregate function |
Date: | 2004-07-05 10:23:28 |
Message-ID: | 003d01c4627a$1ff1a9c0$f664a8c0@najib |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2004-07-05 12:16:23 | Re: creating a complex aggregate function |
Previous Message | Ramesh Yeligar | 2004-07-05 09:40:34 | help required |
From | Date | Subject | |
---|---|---|---|
Next Message | Jeroen T. Vermeulen | 2004-07-05 11:24:20 | Re: [Re] Re: PREPARE and transactions |
Previous Message | Jean-Michel POURE | 2004-07-05 10:15:07 | FreeOSZoo project announcement |