Re: There can be only one! How to avoid the "highlander-problem".

RETURN was missing in the AFTER triggers. here is the corrected version:

----- 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;

RETURN NULL;

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;

RETURN NULL;

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;

RETURN NULL;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER delaft
AFTER DELETE
ON mankind
FOR EACH ROW
EXECUTE PROCEDURE trg_mankind_delaft();

----- end of code

/Erwin