Обсуждение: 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@tardis.cx
Mobile : +44 788 195 1720
This mail is for the addressee only

Mark Nelson <mn@tardis.cx> writes:
> CREATE FUNCTION UpdateNextProjectCode() RETURNS OPAQUE AS '
>   DECLARE
>     project_code          INTEGER;
      ^^^^^^^^^^^^

>     OPEN used_project_codes FOR SELECT project_code FROM projects WHERE
                                         ^^^^^^^^^^^^
>        project_code > 0 ORDER BY project_code ASC;

It's a bad idea to use plpgsql variable names that match field or table
names that you are using in the same function.  plpgsql generally
assumes that you want the variable, not the field or table.  In this
case, what the SQL engine saw was effectively

    SELECT NULL FROM projects WHERE NULL > 0 ORDER BY NULL ASC;

since the variable project_code contains NULL at the time the OPEN
executes.

            regards, tom lane