There can be only one! How to avoid the "highlander-problem". - Mailing list pgsql-general

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