From: | seiliki(at)so-net(dot)net(dot)tw |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Reserve one row for every distinct value in a column |
Date: | 2012-05-16 06:53:21 |
Message-ID: | 20120516065327.A1184F4962D@m5.so-net.net.tw |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi! I am trying to implement a mechanism to reserve the last row for every distinct value in column "c1".
CREATE TABLE table1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2));
CREATE OR REPLACE FUNCTION keep1() RETURNS TRIGGER AS $$
BEGIN
IF (SELECT COUNT(*) FROM table1 WHERE c1=OLD.c1) = 1 THEN
RAISE EXCEPTION 'The last row for c1 = % must be kept!',OLD.c1;
END IF;
RETURN OLD;
END $$ LANGUAGE PLPGSQL STABLE;
CREATE TRIGGER test BEFORE DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE keep1();
INSERT INTO table1 VALUES (1,1),(1,2),(1,3);
With above rows inserted, my desired effect follows:
(1) allowed user operations:
DELETE FROM table1 WHERE c2 IN (1,2); /* Row (1,3) is still kept */
DELETE FROM table1 WHERE c2=2; DELETE FROM table1 WHERE c2=3; /* Row (1,1) is still kept */
DELETE FROM table1 WHERE c1=9; /* We have at least one row with c1=1 that is kept intact */
(2) disallowed user operations:
DELETE FROM table1 WHERE c1=1; /* Exception wanted. Every row for c1=1 would be deleted otherwise. */
DELETE FROM table1 WHERE c2 IN (1,2); DELETE FROM table1 WHERE c2=3; /* The second DELETE must raise exception. */
The above trigger:
(1) It raises exception if table1 has only one row (1,1) and I delete it. This gives expected effect.
(2) When table1 contains 3 rows (1,1),(1,2),(1,3), then all of the following SQL yields unwanted result - they do not raise exception and I can not figure out why the trigger is silenced:
DELETE FROM table1;
DELETE FROM table1 WHERE c1=1;
DELETE FROM table1 WHERE c2 IN (1,2,3);
Would someone please provide me some idea for a working implementation?
Thank you in advance!
CN
From | Date | Subject | |
---|---|---|---|
Next Message | Ajit Pradnyavant | 2012-05-16 06:57:05 | Query regarding Intersect clause |
Previous Message | Evan Martin | 2012-05-16 04:35:28 | Slow queries when functions are inlined |