[GENERAL] PL/pgSQL Problem

[Ron St-Pierre]

Hi, I'm having a problem with one of my functions, where I delete all 
rows containing a particular date and then re-insert a row with that 
same date. When I try this I get a constraint error. This just started 
recently, after upgrading our database from 7.4 to 8.1.4 (now on RH EL).

here's the function:
CREATE OR REPLACE FUNCTION updatesystemCounts() RETURNS void AS '
   DECLARE
       compDate DATE;
       currCount INT;
   BEGIN
       compDate := current_date::date;
       LOOP
           DELETE FROM dm.systemCounts WHERE updateDate::date = 
compDate::date;
           INSERT INTO dm.systemCounts (updateDate) VALUES 
(compDate::date);
           .............
and here's the error:
ERROR:  duplicate key violates unique constraint "systemcounts_pkey"
CONTEXT:  SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES ( $1 
::date)"
PL/pgSQL function "updatesystemcounts" line 8 at SQL statement

The only explanation I can think of is that maybe the newer version of 
postgres needs a COMMIT inside the function.

Any ideas?

Thanks

Ron St.Pierre

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend