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

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