duplicate key triggers possible?

From: Burra <burra(at)colorado(dot)edu>
To: pgsql-novice(at)postgresql(dot)org
Subject: duplicate key triggers possible?
Date: 2001-11-26 18:05:27
Message-ID: Pine.GSO.4.40.0111261056150.9854-100000@ucsub.colorado.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


Hello everyone,
I was wondering if it is possible to do this...

Everytime an attempted insert results in a "duplicate key" error I want to
incriment an integer field of the key already in the database.

...so, like this...

Table "events"
Attribute | Type | Modifier
-----------+--------------------------+-----------------------------------
event | integer | not null default nextval('eventid'::text)
time | timestamp with time zone | not null default now()
rtime | timestamp with time zone |
type | integer | not null default 0 PRIMARY KEY
count | integer | not null default 1

test=> INSERT INTO events (type) VALUES ('10');
INSERT 35533 1
test=> select * from events;
event | time | rtime | type | count
-------+------------------------+-------+------+-------
1 | 2001-11-26 10:42:35-07 | | 10 | 1

test=> INSERT INTO events (type) VALUES ('10');
ERROR: Cannot insert a duplicate key into unique index events_pkey
test=> select * from events;
event | time | rtime | type | count
-------+------------------------+-------+------+-------
1 | 2001-11-26 10:42:35-07 | | 10 | 2

...right now I have a trigger set up "BEFORE INSERT" to ...

CREATE FUNCTION duplicate_count () RETURNS OPAQUE AS '
DECLARE
current_count integer;
BEGIN
-- Select count from events
SELECT INTO current_count count from events where type=NEW.type;
IF current_count ISNULL THEN
RETURN NEW;
END IF;
UPDATE events SET count=(count+1) where type=NEW.type;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER insert_duplicate_count BEFORE INSERT ON events FOR EACH ROW
EXECUTE PROCEDURE duplicate_count();

..but, this doesn't run because of the PRIMARY KEY constraint. Any ideas
on how to get this working?

Thanks.

--------------------[-- burra(at)colorado(dot)edu --]--------------------------

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2001-11-27 01:44:48 Re: Help with triggers
Previous Message John Burski 2001-11-26 17:25:32 Help with triggers