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 <NULL>
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 <NULL>
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(at)cs(dot)york(dot)ac(dot)uk
White Rose Grid, Department of Computer Science,
University of York Heslington, York, YO10 5DD
Reception: +44 1904 432722, Direct Dial: +44 1904 432763

This mail is for the addressee only