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
