Re: PLPGSQL Fetching rows

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