Thread: creating a complex aggregate function

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

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