How to keep the last row of a data set?
От | seiliki@so-net.net.tw |
---|---|
Тема | How to keep the last row of a data set? |
Дата | |
Msg-id | 20121213133234.56113F4816C@m5.so-net.net.tw обсуждение исходный текст |
Ответы |
Re: How to keep the last row of a data set?
Re: How to keep the last row of a data set? Re: How to keep the last row of a data set? |
Список | 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@AMD64:/tmp$ psql -c 'DELETE FROM t1' test Notice: 3 Notice: 3 Notice: 3 Notice: 3 Notice: 3 Notice: 3 DELETE 6 postgres@AMD64:/tmp$ Thank you in advance for helping me out! Best Regards, CN
В списке pgsql-general по дате отправления: