Re: [GENERAL] 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