Re: PL/PGSQL function problem - Mailing list pgsql-novice
From | Mark Hesketh |
---|---|
Subject | Re: PL/PGSQL function problem |
Date | |
Msg-id | 3C47B3A5.50505@iprimus.com.au Whole thread Raw |
In response to | Re: PL/PGSQL function problem ("Josh Berkus" <josh@agliodbs.com>) |
Responses |
Re: PL/PGSQL function problem
(Tom Lane <tgl@sss.pgh.pa.us>)
16k query limit (Torbjörn Andersson <tobbe@embryo.se>) |
List | pgsql-novice |
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=>
pgsql-novice by date: