Reserve one row for every distinct value in a column - Mailing list pgsql-general

From seiliki@so-net.net.tw
Subject Reserve one row for every distinct value in a column
Date
Msg-id 20120516065327.A1184F4962D@m5.so-net.net.tw
Whole thread Raw
Responses Re: Reserve one row for every distinct value in a column
List 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
raiseexception 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

pgsql-general by date:

Previous
From: Evan Martin
Date:
Subject: Slow queries when functions are inlined
Next
From: Chris Angelico
Date:
Subject: Re: Reserve one row for every distinct value in a column