How to keep the last row of a data set?

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

Responses

Browse pgsql-general by date

  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