On 8/9/06, Ron St-Pierre <ron.pgsql@xxxxxxx> wrote:
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.
commit inside function is impossible (by definition), however you can
do subtransaction inside the function and catch the error.
what is the primary key for dm.systemCounts. does it have a default?
merlin
