PLPGSQL Fetching rows

Hi 

I've got the following procedure, it is triggered on a insert and it
works out the next available project code and store it in
working_values.  It looks like the fetch statement in the code does not
not execute.


Example run - 

wrg=# INSERT INTO projects  VALUES (1,'proj1');
NOTICE:  Project_code is 
NOTICE:  start_code is 1
NOTICE:   NOT FOUND
NOTICE:   UPDATING WORKING VALUES 2
INSERT 50262 1

wrg=# select next_project_code from working_values ;
 next_project_code 
-------------------
                 2
(1 row)

wrg=# INSERT INTO projects  VALUES (2,'proj2');
NOTICE:  Project_code is 
NOTICE:  start_code is 1
NOTICE:   NOT FOUND
NOTICE:   UPDATING WORKING VALUES 2
INSERT 50263 1

wrg=# select next_project_code from working_values ;
 next_project_code 
-------------------
                 2
(1 row)


I would expect the next_project_code to be 3 

Table looks as follows - 

wrg=# \d projects 
                 Table "public.projects"
       Column        |          Type          | Modifiers 
---------------------+------------------------+-----------
 project_code        | integer                | not null
 project_description | character varying(255) | not null
Indexes: projects_pkey primary key btree (project_code)
Triggers: updatenextprojectcode



Any Ideas

Mark.

----------------- Code ----------------


CREATE FUNCTION UpdateNextProjectCode() RETURNS OPAQUE AS '

 /* * * 
  *
  *  OK calcualates the next free project_code and stores it 
  *  in the field next_project_code in the table working_values
  *
  * * */  
   
  DECLARE
  
    start_project_code    INTEGER;
    end_project_code      INTEGER;
    match                 INTEGER;
    project_code          INTEGER;
    rec                   RECORD;
    used_project_codes    refcursor; 
     
  BEGIN

    start_project_code := 1;
    end_project_code   := 65533;

    OPEN used_project_codes FOR SELECT project_code FROM projects WHERE
       project_code > 0 ORDER BY project_code ASC;
       
    match:=0; 
    FETCH used_project_codes INTO project_code;
  
  
    WHILE (match = 0)  LOOP
    
      /* DEBUG */
      RAISE NOTICE ''Project_code is %'', project_code;
      RAISE NOTICE ''start_code is %'', start_project_code;
      IF NOT FOUND THEN
         RAISE NOTICE '' NOT FOUND'';
         start_project_code=start_project_code + 1; 
         match=1;
      ELSE
      RAISE NOTICE ''IN FOR LOOP'' ;
         
        IF (start_project_code > end_project_code) THEN
            RAISE EXCEPTION ''Out of project codes'';
        END IF;
      
        IF (project_code = start_project_code) THEN 
           start_project_code:= start_project_code + 1;
           RAISE NOTICE ''Incrementing start_project_code'';
        ELSIF (project_code > start_project_code) THEN  
          RAISE NOTICE ''Setting match to 1''; 
          match:=1;       
         
        END IF; 
      END IF;
      
      FETCH used_project_codes INTO project_code;

    END LOOP;
    
    RAISE NOTICE '' UPDATING WORKING VALUES %'',start_project_code;
    UPDATE working_values SET next_project_code=start_project_code;
    CLOSE used_project_codes;
    
    RETURN NULL;
  END;

' LANGUAGE 'plpgsql';

/* * *
 *
 * Set up the trigger
 *
 * * */
 
 CREATE TRIGGER UpdateNextProjectCode AFTER INSERT ON projects 
  FOR EACH ROW EXECUTE PROCEDURE UpdateNextProjectCode(); 




-- 
-----------------------------------
Mark Nelson - mn@tardis.cx
Mobile : +44 788 195 1720
This mail is for the addressee only