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 --]--------------------------
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 |