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 LOOPThe 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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
