On 12 Sep 2009, at 11:58, Alban Hertroys wrote:
> On 12 Sep 2009, at 24:17, db.subscriptions@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!
