How to keep the last row of a data set?

Поиск
Список
Период
Сортировка
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 по дате отправления:

Предыдущее
От: James Cowell
Дата:
Сообщение: Re: Corrupt indexes on slave when using pg_bulkload on master
Следующее
От: wd
Дата:
Сообщение: Re: Corrupt indexes on slave when using pg_bulkload on master