Thread: creating a complex aggregate function

creating a complex aggregate function

From
"Najib Abi Fadel"
Date:
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.
 
 
 
 

Re: creating a complex aggregate function

From
Richard Huxton
Date:
Najib Abi Fadel wrote:
> Hi
> i have an ordered table of dates let's say:

No you don't (tables aren't ordered), and I think that's what's going to
cause you trouble.

> 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 :

My C is even rustier than yours, but you're assuming here that the dates
you receive will be passed to you in order. I don't think PG guarantees
this (perhaps with the exception of an explicit sort in a subselect).
That's not to say it won't work when you test it, just that the order
isn't guaranteed so you can't rely on it.

Now, for sum()/min() etc this doesn't matter, you only need to compare
the current value with a "running total", but in your case you'll need
to match against many different groups.

I think what you want here is a set-returning function, doing something
like:

last_date := null;
FOR myrow IN SELECT id,tgt_date FROM my_dates ORDER BY tgt_date LOOP
   diff := myrow.tgt_date - last_date;
   IF diff = 7 THEN
     last_date:=myrow.tgt_date;
   ELSE
     -- Assemble a result row and return it here
   END IF;
END LOOP

The above is (roughly) plpgsql syntax.

> text * concat(,){} // NOT IMPLEMENTED (HOW TO DO IT ??)

The function you want is textcat(text,text). I believe all of the
operators (|| + - etc) have equivalent functions. They're not listed in
the user documentation, but "\df text*" will show you them.

HTH
--
   Richard Huxton
   Archonet Ltd

Re: creating a complex aggregate function

From
"Najib Abi Fadel"
Date:
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 -----
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.