From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Najib Abi Fadel <nabifadel(at)usj(dot)edu(dot)lb> |
Cc: | generalpost <pgsql-general(at)postgresql(dot)org>, developPost <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: creating a complex aggregate function |
Date: | 2004-07-05 12:16:23 |
Message-ID: | 40E94697.4000405@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Najib Abi Fadel | 2004-07-05 12:18:12 | Re: Bug in function to_char() !! |
Previous Message | Najib Abi Fadel | 2004-07-05 10:23:28 | creating a complex aggregate function |
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Jowett | 2004-07-05 12:17:50 | Re: [Re] Re: PREPARE and transactions |
Previous Message | Jeroen T. Vermeulen | 2004-07-05 11:55:38 | Re: [Re] Re: PREPARE and transactions |