Re: PL/pgSQL Problem

On 8/9/06, Ron St-Pierre <ron(dot)pgsql(at)shaw(dot)ca> 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