[GENERAL] PL/pgSQL Problem

2006-08-09 Thread 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

Re: [GENERAL] PL/pgSQL Problem

2006-08-09 Thread Michael Fuhr 
On Wed, Aug 09, 2006 at 10:33:02AM -0700, Ron St-Pierre wrote:
> 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

Is the table's primary key a serial column?  If so then the sequence
might be out of sync with the values in the table, in which case
you'll need to adjust the sequence's value with ALTER SEQUENCE or
setval().

-- 
Michael Fuhr

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PL/pgSQL Problem

2006-08-09 Thread Merlin Moncure 

On 8/9/06, Ron St-Pierre <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] PL/pgSQL Problem

2006-08-09 Thread Ron St-Pierre 

Michael Fuhr wrote:

On Wed, Aug 09, 2006 at 10:33:02AM -0700, Ron St-Pierre wrote:
  

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



Is the table's primary key a serial column?  If so then the sequence
might be out of sync with the values in the table, in which case
you'll need to adjust the sequence's value with ALTER SEQUENCE or
setval().

  

Aha, that was exactly what the problem was.

Thanks!

Ron

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] PL/pgsql problem in 6.5

1999-06-16 Thread Anonymous 

Hi everybody,

I have just installed PostgreSQL 6.5 and encountered problems with
PL/pgsql: On an insert into a table which has a before-insert trigger
I get the following error message:

ERROR:  Load of file /usr/local/pgsql/lib/plpgsql.so failed: 
/usr/local/pgsql/lib/plpgsql.so:
 undefined symbol: SPI_push

What's wrong with my installation?

The trigger looks like this:

DECLARE
  contentCONSTANT VARCHAR (1) DEFAULT 'N';
  new_series art.series%TYPE;
  new_rating art.rating%TYPE;
  anzahl INT4;
  max_indx   art.indx%TYPE;

BEGIN
  new_series := NEW.series;
  new_rating := NEW.rating;

  SELECT COUNT (*)
INTO anzahl
FROM series
   WHERE ke_content = content
 AND series = new_series;

  IF anzahl = 0 THEN
SELECT NVL (MAX (indx), 0) + 1
  INTO max_indx
  FROM series;

INSERT INTO series (ke_content, series, indx, rating)
VALUES (content, new_series, max_indx, new_rating);
  END IF;

  RETURN NEW;
END;

 --
Oliver Graf
Oberhofstr. 11
D-44575 Castrop-Rauxel, Germany
Phone: +49 2305 14230
http://www.ruhr.de/home/casbah/