Skip site navigation (1) Skip section navigation (2)

Re: PL/PGSQL function problem

From: Mark Hesketh <renmark(at)iprimus(dot)com(dot)au>
To: pgsql-novice(at)postgresql(dot)org
Cc: Josh Berkus <josh(at)agliodbs(dot)com>
Subject: Re: PL/PGSQL function problem
Date: 2002-01-18 05:33:25
Message-ID: 3C47B3A5.50505@iprimus.com.au (view raw or flat)
Thread:
Lists: 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=>



In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2002-01-18 05:52:17
Subject: Re: PL/PGSQL function problem
Previous:From: Oliver ElphickDate: 2002-01-18 01:19:58
Subject: Re: NULLs on datetime problem (for me at least :) )

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group