Thread: PL/PGSQL function problem
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';
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 ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
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=>
Mark Hesketh <renmark@iprimus.com.au> writes: > [ very strange-looking log trace ] I'd almost think that that trace is a mixture of output-to-the-client with output-to-the-postmaster-log. What are you showing us, exactly? And how did you start the postmaster? regards, tom lane
Tom Lane wrote: >Mark Hesketh <renmark@iprimus.com.au> writes: > >>[ very strange-looking log trace ] >> > >I'd almost think that that trace is a mixture of output-to-the-client >with output-to-the-postmaster-log. What are you showing us, exactly? >And how did you start the postmaster? > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly > Problem solved... it was a simple bug in my function... confusing tho... ;) Thanks for the interest. Mark
Hi, There is a 16k limit on how large an SQL-query can be. How do I increase this and is the default larger i 7.2? Regards, Torbjörn Andersson --------------------------------------------------- Embryo Communication phone: +46 (0) 31-774 39 11(00) Kungsgatan 7a fax: +46 (0)31 774 07 80 S-411 19 Göteborg mobile: 0708-30 70 04 Sweden home: http://www.embryo.se/ mail: torbjorn.andersson@embryo.se --------------------------------------------------- "Att idag tänka annorlunda än igår skiljer den vise från den envise." John Steinbeck
Torbj=?ISO-8859-1?B?9g==?=rn Andersson <tobbe@embryo.se> writes: > There is a 16k limit on how large an SQL-query can be. That limit went away in 7.0. regards, tom lane
02-01-30 17.00 Tom Lane tgl@sss.pgh.pa.us > Torbj=?ISO-8859-1?B?9g==?=rn Andersson <tobbe@embryo.se> writes: >> There is a 16k limit on how large an SQL-query can be. > > That limit went away in 7.0. I found it in 7.1.1 but it is gone in 7.1.3 which means my problem is solved. Regards Torbjörn Andersson --------------------------------------------------- Embryo Communication phone: +46 (0) 31-774 39 11(00) Kungsgatan 7a fax: +46 (0)31 774 07 80 S-411 19 Göteborg mobile: 0708-30 70 04 Sweden home: http://www.embryo.se/ mail: torbjorn.andersson@embryo.se --------------------------------------------------- "Att idag tänka annorlunda än igår skiljer den vise från den envise." John Steinbeck