problem with plpgsql

From: Pascal Bourguignon <pjb(at)informatimago(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: problem with plpgsql
Date: 2001-08-17 03:32:27
Message-ID: 20010817033227.110275A6FA@thalassa.informatimago.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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------

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Pedro Alves 2001-08-17 08:46:16 Bug Report
Previous Message Tom Lane 2001-08-16 22:22:07 Re: Using nulls with earthdistance operator crashes backend (patch)