seiliki@so-net.net.tw writes:
> I am trying to implement a mechanism that prohibits the last row of a data set from being deleted.
> 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;
The reason that doesn't work is you marked it "stable", so it always
sees the starting state of the outer query.
Mind you, even with that oversight fixed, this approach will do little
to save you from concurrent-update situations. That is, transaction A
could delete some of the rows with c1=1, and transaction B could
concurrently delete the rest, and neither transaction will see a reason
why it shouldn't commit.
regards, tom lane