On Thu, Dec 15, 2011 at 3:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> They're checked at the instant that a row is inserted or updated.
> If you really need a deferred check, you'll need to build it yourself
> using an AFTER ROW trigger.
Thank you for taking time to answer my novice question.
The AFTER ROW trigger version is what I expected.
CREATE TABLE foos (
bar INTEGER
);
CREATE FUNCTION check_foo_cardinality() RETURNS trigger AS $$
BEGIN
IF (SELECT count(*) FROM foos) < 1 THEN
RAISE EXCEPTION 'at least one row required';
END IF;
RETURN NULL;
END
$$ LANGUAGE plpgsql;
CREATE CONSTRAINT TRIGGER check_cardinality
AFTER DELETE ON foos
INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE check_foo_cardinality();
INSERT INTO foos VALUES (1);
DELETE FROM foos; -- => ERROR: at least one row required
Best,