PL/PGSQL function problem - Mailing list pgsql-novice

Hi there,

Can someone tell me if there's a fairly obvious reason why when I run a
function I've written it runs twice?

Here's the code (replete with debug - sorry). The basic structure has a
number of nested 'for' loops. The trace debug
reveals that it's running twice through yet I'm only issueing "select
generateshiftcandidatedata();" x 1.

Any help would be great.

Mark

create function generateShiftCandidateData() returns integer as '
DECLARE
  rolesRec RECORD;
  skillsRec RECORD;
  availRec RECORD;
  shiftRec RECORD;
  conditionRec RECORD;
  conditionsToMeet BOOLEAN := TRUE;
    rowcount integer := 0;
    availCount integer := 0;
    counter integer := 0;
    available boolean := FALSE;
    done boolean := FALSE;

begin
    /* find all roles */
    --for rolesRec in select * from role loop
    for rolesRec in select * from role where role_id = 20 loop
      raise notice ''Checking for role %... '',rolesRec.role_id;

        for skillsRec in select * from employee_skill where "RoleID" =
rolesRec.role_id loop
          if not done then
                get diagnostics rowcount = ROW_COUNT;
                if rowcount > 0 then
                raise notice '' No. of employees for Role % = %'',
rolesRec.role_id, rowcount;
                end if;
            done := not done;
            end if;

            /* find all availabilities for employees found */
            raise notice ''checking availability for
employee(%)...'',skillsRec.EmployeeID;
            select into availCount count(*) from availability where
"EmployeeID" = skillsRec.EmployeeID;
            raise notice ''... available for % days'', availCount;
            for availRec in select * from availability where
"EmployeeID" = skillsRec.EmployeeID loop

                /* find all shifts for this role (does ordering matter?) */
                for shiftRec in select * from shift where "RoleID" =
rolesRec.role_id order by "StartTime" loop

                    /*
                    for conditionRec in select * from shift_condition
where "ShiftID" = shiftRec.ShiftID loop

                        if conditionsToMeet then

                        end if;

                    end loop;
                    */

                    /* now, find candidates */
                    available := isAvailable(shiftRec.StartTime,
shiftRec.StopTime, availRec.FromTime, availRec.ToTime);
                    if available then
                      raise notice '' employee % '', availRec.EmployeeID;
                        raise notice '' is available to perform shift
%'', shiftRec.ShiftID;
                        perform recordCandidate(availRec.EmployeeID,
shiftRec.RoleID, shiftRec.ShiftID, shiftRec.Duration);
                    else
                      --raise notice '' Employee % was not available for
shift %'',availRec.EmployeeID, shiftRec.ShiftID;
                    end if;
              end loop;
            end loop;
      end loop;
      counter := counter + 1;
      raise notice ''No of roles processed = %'',counter;
    end loop;

  /* all was fine... */
    RETURN 0;
end;
' language 'plpgsql';