PLPGSQL Fetching rows - Mailing list pgsql-general

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@tardis.cx
Mobile : +44 788 195 1720
This mail is for the addressee only