On 12 Sep 2009, at 11:58, Alban Hertroys wrote:
On 12 Sep 2009, at 24:17, db.subscripti...@shepherdhill.biz wrote:
Hi,
I have a loop of the form:
FOR rec IN SELECT code FROM staff WHERE shiftgroup = NEW.groupe
ORDER BY code LOOP
WHILE sdate <= NEW.todate LOOP
SELECT INTO starty,endy,nday resumetime,closetime,nextday FROM
shifts WHERE shift = NEW.shift;
restime := sdate + starty;
IF nday = true THEN
clstime := sdate + interval '1 day' + endy;
ELSE
clstime := sdate + endy;
END IF;
INSERT INTO shiftsexp(id,staff,resumetime,closetime) VALUES
(NEW.id,rec.code,restime,clstime);
sdate := sdate + interval '1 day';
END LOOP;
END LOOP;
Surprisingly, the outer loop (For .. Loop) does not loop while only
the WHILE ... Loop works.
What makes you think that? There's nothing in your code that uses
the record from your outer loop, so how do you know it doesn't loop?
Did you raise notices to check? Why do you even have that loop, it
doesn't appear to serve a purpose?
Ah wait, ENOCOFFEE... You do use the rec variable.
What you don't do is reset the sdate to the value it had at the start
of your outer loop, so you end up immediately meeting the condition of
the WHILE expressions' loop after the first iteration through your FOR
loop. So it loops through the outer loop, but after the first time
there's no work left to be done inside it.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4aab752a13787908612439!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
