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------
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) |