problem with plpgsql - Mailing list pgsql-bugs

I've got the  following problem with a plpgsql  function. I believe it
denotes a bug with plpgsql.

I'm trying to  write a function to either insert a  new row, or update
an existing row. However, the test  "if not found" is always true, and
I get duplicate rows instead of one updated row.

I've tried  with various  forms for the  first select with  always the
same  bad result. (select  into cnt  count(*) from  lim... ;  if cnt=0
then..., among others)


Both with:
    psql (PostgreSQL) 7.0.2
    contains readline, history, multibyte support
    Portions Copyright (c) 1996-2000, PostgreSQL, Inc
    Portions Copyright (c) 1996 Regents of the University of California
    Read the file COPYRIGHT or use the command \copyright to see the
    usage and distribution terms.

and with:
    psql (PostgreSQL) 7.0.3
    contains readline, history, multibyte support
    Portions Copyright (c) 1996-2000, PostgreSQL, Inc
    Portions Copyright (c) 1996 Regents of the University of California
    Read the file COPYRIGHT or use the command \copyright to see the
    usage and distribution terms.

------------------------------------------------------------------------
lim=> delete from lim where login='pjb';
DELETE 2
lim=> drop function lim_update(text,text,text,date);
DROP
lim=> create function lim_update(text,text,text,date) returns integer as '
lim'>     declare
lim'>         plogin alias for $1;
lim'>         pip    alias for $2;
lim'>         pmac   alias for $3;
lim'>         pdate  alias for $4;
lim'>         rec    record;
lim'>         cnt    integer:=0;
lim'>     begin
lim'>         select into rec *
lim'>             from lim
lim'>             where login=plogin and ip=pip and mac=pmac;
lim'>
lim'>         if not found then
lim'>             insert into lim (login,ip,mac,last_date,logcnt)
lim'>                 values (plogin,pip,pmac,pdate,1);
lim'>             return 1;
lim'>         end if;
lim'>
lim'>         cnt=rec.logcnt;
lim'>         cnt:=cnt+1;
lim'>         update lim
lim'>             set last_date=pdate,
lim'>                 logcnt=cnt
lim'>             where login=plogin and ip=pip and mac=pmac;
lim'>         return cnt;
lim'>     end;
lim'> ' language 'plpgsql';
CREATE
lim=> select lim_update('pjb','212.87.205.57','12:34:45:09:12:43','2001-08-12 13:14:15');
 lim_update
------------
          1
(1 row)

lim=> select * from lim;
  login   |       ip        |        mac        | last_date  | logcnt
----------+-----------------+-------------------+------------+--------
 pjb      | 212.87.205.57   | 12:34:45:09:12:43 | 2001-08-12 |      1
(1 row)

lim=> select lim_update('pjb','212.87.205.57','12:34:45:09:12:43','2001-08-12 14:14:14');
 lim_update
------------
          1
(1 row)

lim=> select * from lim;
  login   |       ip        |        mac        | last_date  | logcnt
----------+-----------------+-------------------+------------+--------
 pjb      | 212.87.205.57   | 12:34:45:09:12:43 | 2001-08-12 |      1
 pjb      | 212.87.205.57   | 12:34:45:09:12:43 | 2001-08-12 |      1
(2 rows)


------------------------------------------------------------------------
### SGDB Administrator:
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
         '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
         HANDLER plpgsql_call_handler
         LANCOMPILER 'PL/pgSQL';

### DB Owner:

drop table lim;
create table lim (
    login     char(8),
    ip        char(15),
    mac       char(17),
    last_date date,
    logcnt    integer
);

delete from lim where login='pjb';
drop function lim_update(text,text,text,date);
create function lim_update(text,text,text,date) returns integer as '
    declare
        plogin alias for $1;
        pip    alias for $2;
        pmac   alias for $3;
        pdate  alias for $4;
        rec    record;
        cnt    integer:=0;
    begin
        select into rec *
            from lim
            where login=plogin and ip=pip and mac=pmac;

        if not found then
            insert into lim (login,ip,mac,last_date,logcnt)
                values (plogin,pip,pmac,pdate,1);
            return 1;
        end if;

        cnt=rec.logcnt;
        cnt:=cnt+1;
        update lim
            set last_date=pdate,
                logcnt=cnt
            where login=plogin and ip=pip and mac=pmac;
        return cnt;
    end;
' language 'plpgsql';
select lim_update('pjb','212.87.205.57','12:34:45:09:12:43','2001-08-12 13:14:15');
select * from lim;
select lim_update('pjb','212.87.205.57','12:34:45:09:12:43','2001-08-12 14:14:14');
select * from lim;
------------------------------------------------------------------------


--
__Pascal_Bourguignon__              (o_ Software patents are endangering
()  ASCII ribbon against html email //\ the computer industry all around
/\  and Microsoft attachments.      V_/ the world http://lpf.ai.mit.edu/
1962:DO20I=1.100  2001:my($f)=`fortune`;  http://petition.eurolinux.org/

-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCS/IT d? s++:++(+++)>++ a C+++  UB+++L++++$S+X++++>$ P- L+++ E++ W++
N++ o-- K- w------ O- M++$ V PS+E++ Y++ PGP++ t+ 5? X+ R !tv b++(+)
DI+++ D++ G++ e+++ h+(++) r? y---? UF++++
------END GEEK CODE BLOCK------