Re: PL/PGSQL function problem

Josh Berkus wrote:

>Mark,
>
>Whoa! Based on this, you are ready to join the pgsql-sql list. This is not a
> novice question!
>
>>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.
>>
>
>Um ... how are you calling the function? From psql? And when you say "it runs
> twice", what do you mean, exactly? Myabe you could paste the relevant portion
> of the debug output?
>
>-Josh
>
Thanks for the reply Josh (x2!). Errm... i've change the script slightly
- improved the quality of the debug output - the code is the same and
yes, i'm still seeing it run twice: witness the "STARTING FUNCTION"
output x 2. This is printed once at the start of the function - or
rather it's supposed to be... ;)

Thanks for your help.

Here's the function:

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

raise notice ''STARTING FUNCTION...'';

/*if counter >= 1 then
RETURN 0;
end if;*/

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';

Here's the output:

>
NOTICE: STARTING FUNCTION...
NOTICE: STARTING FUNCTION...
NOTICE: Checking for role 20...
NOTICE: Checking for role 20...
NOTICE: checking availability for employee(3)...
NOTICE: checking availability for employee(3)...
NOTICE: ... available for 5 days
NOTICE: ... available for 5 days
NOTICE: 10 lies before shift start of 7
NOTICE: 10 lies before shift start of 8
NOTICE: 10 lies before shift start of 8.5
NOTICE: employee 3
NOTICE: is available to perform shift 9
NOTICE: 10 lies before shift start of 7
NOTICE: 10 lies before shift start of 8
NOTICE: 10 lies before shift start of 8.5
NOTICE: employee 3
NOTICE: is available to perform shift 9
NOTICE: employee 3
NOTICE: is available to perform shift 8
NOTICE: 16 lies beyond shift boundary of 19.5
NOTICE: 16 lies beyond shift boundary of 20
NOTICE: 16 lies beyond shift boundary of 20.5
NOTICE: 16 lies beyond shift boundary of 23
NOTICE: 16 lies beyond shift boundary of 23
NOTICE: 12 lies before shift start of 7
NOTICE: 12 lies before shift start of 8
NOTICE: 12 lies before shift start of 8.5
NOTICE: employee 3
NOTICE: is available to perform shift 9
ERROR: Cannot insert a duplicate key into unique index sc_uk
DEBUG: Last error occured while executing PL/pgSQL function recordcandidate
DEBUG: line 9 at SQL statement
NOTICE: employee 3
NOTICE: is available to perform shift 8
NOTICE: 16 lies beyond shift boundary of 19.5
NOTICE: 16 lies beyond shift boundary of 20
NOTICE: 16 lies beyond shift boundary of 20.5
NOTICE: 16 lies beyond shift boundary of 23
NOTICE: 16 lies beyond shift boundary of 23
NOTICE: 12 lies before shift start of 7
NOTICE: 12 lies before shift start of 8
NOTICE: 12 lies before shift start of 8.5
NOTICE: employee 3
NOTICE: is available to perform shift 9
ERROR: Cannot insert a duplicate key into unique index sc_uk
schmick_dev_db=>