From: | seiliki(at)so-net(dot)net(dot)tw |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to keep the last row of a data set? |
Date: | 2012-12-13 13:32:29 |
Message-ID: | 20121213133234.56113F4816C@m5.so-net.net.tw |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am trying to implement a mechanism that prohibits the last row of a data set from being deleted.
CREATE TABLE t1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2));
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
My desired effect:
Case 1, Permit this SQL to be executed:
DELETE FROM t1 WHERE c1=1 AND c2 <> 2;
This SQL keeps one row whose column c1 holds value "1". It does not hurt.
Case 2, Raise exception if users attempt to run this SQL:
DELETE FROM t1 WHERE c1=1;
This SQL attempts to delete all rows having value "1" in column c1. It must be automatically aborted.
The following trigger protects nothing:
CREATE OR REPLACE FUNCTION tfd() RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE '%',(SELECT COUNT(*) FROM t1 WHERE c1=OLD.c1);
IF (SELECT COUNT(*) FROM t1 WHERE c1=OLD.c1) = 1 THEN
RAISE EXCEPTION 'Must keep at least 1 row for c1="%"',OLD.c1;
END IF;
RETURN OLD;
END $$ LANGUAGE PLPGSQL STABLE;
CREATE TRIGGER td BEFORE DELETE ON t1 FOR EACH ROW EXECUTE PROCEDURE tfd();
postgres(at)AMD64:/tmp$ psql -c 'DELETE FROM t1' test
Notice: 3
Notice: 3
Notice: 3
Notice: 3
Notice: 3
Notice: 3
DELETE 6
postgres(at)AMD64:/tmp$
Thank you in advance for helping me out!
Best Regards,
CN
From | Date | Subject | |
---|---|---|---|
Next Message | wd | 2012-12-13 13:46:43 | Re: Corrupt indexes on slave when using pg_bulkload on master |
Previous Message | David Noel | 2012-12-13 13:01:41 | initdb error |