Reserve one row for every distinct value in a column

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

Responses

Browse pgsql-general by date

  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