Hi group!
In the course of trying to create a cleanly formated posting that would
make my problem understandable I have eventually solved it myself. :)
I now post the solution instead, maybe it is of interest to someone. :)
Here is a showcase how to avoid to the "highlander-problem".
Imagine a male, monarchistic world. Every people must have one (and only
one) king at _all_ times. This implementation aims to enforce these
rules as cleanly and swiftly as possible..
----- begin of code
CREATE TABLE mankind
(
man_id integer primary key,
people_id integer NOT NULL, -- references table people
.., but that's irrelevant here ..
king boolean NOT NULL DEFAULT false
);
---- Only one king per people ----
/* But no partial UNIQUE INDEX, because my solution needs temporary
"duplicates". Peoples will have to trust the triggers.
I _could_ implement it with a DEFERRED table constraint, IF partial
indices were supported with table constraints,
but they are not in pg 8.1.x or 8.2.x. Pseudo-Code example:
ALTER TABLE mankind
ADD CONSTRAINT mankind_people_uni_king_idx UNIQUE (people_id) WHERE
king[ = true]
DEFERRABLE INITIALLY DEFERRED;
I create (a non-unique) index anyway, to speed up the triggers.
*/
CREATE INDEX mankind_king_idx
ON mankind (people_id)
WHERE king;
---- trigger BEFORE UPDATE ---- To keep it simple we make world racist.
Men cannot migrate.
CREATE OR REPLACE FUNCTION trg_mankind_upbef()
RETURNS "trigger" AS
$BODY$
BEGIN
IF NEW.people_id <> OLD.people_id THEN -- NOT NULL allows "<>"
RAISE EXCEPTION 'This is a racist world! Men cannot migrate.';
END IF;
IF NEW.man_id <> OLD.man_id THEN -- NOT NULL allows "<>"
RAISE EXCEPTION 'A man has only one life and cannot change his
identity.';
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER upbef
BEFORE UPDATE
ON mankind
FOR EACH ROW
EXECUTE PROCEDURE trg_mankind_upbef();
---- trigger AFTER UPDATE ----
CREATE OR REPLACE FUNCTION trg_mankind_upaft()
RETURNS "trigger" AS
$BODY$
DECLARE
kings int4;
BEGIN
IF NEW.king <> OLD.king THEN -- NOT NULL allows
"<>"
kings := count(*)::int4 FROM mankind WHERE people_id = NEW.people_id
AND king;
raise warning '%', kings;
IF kings = 1 THEN
--do nothing;
ELSIF kings < 1 THEN
RAISE EXCEPTION 'You must make another man king to get rid of
the old king!';
ELSIF kings > 1 THEN
UPDATE mankind
SET king = FALSE
WHERE people_id = NEW.people_id
AND man_id <> NEW.man_id -- God save the
new king!
AND king;
END IF;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER upaft
AFTER UPDATE
ON mankind
FOR EACH ROW
EXECUTE PROCEDURE trg_mankind_upaft();
---- trigger BEFORE INSERT ----
CREATE OR REPLACE FUNCTION trg_mankind_insbef()
RETURNS "trigger" AS
$BODY$
BEGIN
IF NOT EXISTS (SELECT 1 FROM mankind WHERE people_id = NEW.people_id) THEN
NEW.king := true; -- firstborn is
always king.
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER insbef
BEFORE INSERT
ON mankind
FOR EACH ROW
EXECUTE PROCEDURE trg_mankind_insbef();
---- trigger AFTER INSERT ----
CREATE OR REPLACE FUNCTION trg_mankind_insaft()
RETURNS "trigger" AS
$BODY$
DECLARE
kings int4;
BEGIN
kings := count(*)::int4 FROM mankind WHERE people_id = NEW.people_id AND
king;
IF kings = 1 THEN
--do nothing;
ELSIF kings > 1 THEN
UPDATE mankind
SET king = FALSE
WHERE people_id = NEW.people_id
AND man_id <> NEW.man_id -- God save the
new king!
AND king;
ELSIF kings < 1 THEN -- actually,
should never occur, because of trigger BEFORE INSERT
UPDATE mankind
SET king = TRUE
WHERE man_id = NEW.man_id; -- the new man is
as good a king as any.
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER insaft
AFTER INSERT
ON mankind
FOR EACH ROW
EXECUTE PROCEDURE trg_mankind_insaft();
---- trigger AFTER DELETE ---- (if old king dies)
CREATE OR REPLACE FUNCTION trg_mankind_delaft()
RETURNS "trigger" AS
$BODY$
BEGIN
-- We trust the triggers and do not check if there was another king, as
there can be only one.
-- AND NOT EXISTS (SELECT 1 FROM mankind WHERE people_id =
OLD.people_id AND king)
IF OLD.king THEN
UPDATE mankind SET king = true
WHERE man_id = (SELECT man_id FROM mankind WHERE people_id =
OLD.people_id LIMIT 1);
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER delaft
AFTER DELETE
ON mankind
FOR EACH ROW
EXECUTE PROCEDURE trg_mankind_delaft();
----- end of code
This solution is tested and works. Of course, there is always room for
improvement. So if you spot something, don't hesitate to point it out.
Any other feedback is welcome as well, of course.
Thanks for all your virtual help so far! ;)
Regards
Erwin
