Closure Table method for hierarchical data with PostGreSQL

hierarchical_data.sql

DROP TABLE IF EXISTS page CASCADE;
CREATE TABLE IF NOT EXISTS page
(
  id serial NOT NULL PRIMARY KEY,
  title character(64) NOT NULL,
  parent_id integer REFERENCES page
);

DROP TABLE IF EXISTS page_hierarchy;
CREATE TABLE IF NOT EXISTS page_hierarchy(
	id serial NOT NULL,
	parent_id integer NOT NULL REFERENCES page ON DELETE CASCADE,
	child_id integer NOT NULL REFERENCES page ON DELETE CASCADE,
	depth integer NOT NULL
);

CREATE OR REPLACE FUNCTION page_hierarchy_ai() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
    INSERT INTO page_hierarchy (parent_id, child_id, depth) VALUES (NEW.id, NEW.id, 0);
    INSERT INTO page_hierarchy (parent_id, child_id, depth)
	SELECT x.parent_id, NEW.id, x.depth + 1
	FROM page_hierarchy x
	WHERE x.child_id = NEW.parent_id;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
DROP TRIGGER IF EXISTS page_hierarchy_ai ON page;
CREATE TRIGGER page_hierarchy_ai AFTER INSERT ON page
FOR EACH ROW EXECUTE PROCEDURE page_hierarchy_ai();



CREATE OR REPLACE FUNCTION page_hierarchy_bu() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
    IF NEW.id <> OLD.id THEN
        RAISE EXCEPTION 'Changing ids is forbidden.';
    END IF;
    IF NOT OLD.parent_id IS DISTINCT FROM NEW.parent_id THEN
        RETURN NEW;
    END IF;
    IF NEW.parent_id IS NULL THEN
        RETURN NEW;
    END IF;
    PERFORM 1 FROM page_hierarchy WHERE ( parent_id, child_id ) = ( NEW.id, NEW.parent_id );
    IF FOUND THEN
        RAISE EXCEPTION 'Update blocked, because it would create loop in tree.';
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
DROP TRIGGER IF EXISTS page_hierarchy_bu ON page;
CREATE TRIGGER page_hierarchy_bu BEFORE UPDATE ON page
FOR EACH ROW EXECUTE PROCEDURE page_hierarchy_bu();

CREATE OR REPLACE FUNCTION page_hierarchy_ad() RETURNS TRIGGER AS
$BODY$
DECLARE BEGIN
	-- TODO: Check if has child and raise exception? Move all children to superior level ? leave as-is ?
	-- Here: should be achieved with CASCADE
	DELETE FROM page_hierarchy WHERE parent_id = OLD.id OR child_id = OLD.id;
	RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER page_hierarchy_ad AFTER DELETE ON page
FOR EACH ROW EXECUTE PROCEDURE page_hierarchy_ad();


CREATE OR REPLACE FUNCTION page_hierarchy_bd() RETURNS TRIGGER AS
$BODY$
DECLARE BEGIN
	-- Check if has child and raise exception
	-- Useless here ! use REFERENCES at table definition
	PERFORM 1 FROM page WHERE parent_id = OLD.id;
        IF FOUND THEN
            RAISE EXCEPTION 'Page has child';
        END IF;
	RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER page_hierarchy_bd AFTER DELETE ON page
FOR EACH ROW EXECUTE PROCEDURE page_hierarchy_bd();

CREATE OR REPLACE FUNCTION page_hierarchy_au() RETURNS TRIGGER AS
$BODY$
    DECLARE
    BEGIN
        IF NOT OLD.parent_id IS DISTINCT FROM NEW.parent_id THEN
            RETURN NEW;
        END IF;
        IF OLD.parent_id IS NOT NULL THEN
            DELETE FROM page_hierarchy WHERE id in (
                SELECT r2.id FROM page_hierarchy r1
                join page_hierarchy r2 on r1.child_id = r2.child_id
                WHERE r1.parent_id = NEW.id AND r2.depth > r1.depth
            );
        END IF;
        IF NEW.parent_id IS NOT NULL THEN
            INSERT INTO page_hierarchy (parent_id, child_id, depth)
            SELECT r1.parent_id, r2.child_id, r1.depth + r2.depth + 1
            FROM
                page_hierarchy r1,
                page_hierarchy r2
            WHERE
                r1.child_id = NEW.parent_id AND
                r2.parent_id = NEW.id;
        END IF;
        RETURN NEW;
    END;
$BODY$
LANGUAGE 'plpgsql';
DROP TRIGGER IF EXISTS page_hierarchy_au ON page;
CREATE TRIGGER page_hierarchy_au AFTER UPDATE ON page
FOR EACH ROW EXECUTE PROCEDURE page_hierarchy_au();

-- page functions
CREATE OR REPLACE FUNCTION page_add(title character(64), parent_id integer) RETURNS integer AS 
$BODY$
BEGIN
	INSERT INTO page(title, parent_id) VALUES(title, parent_id);
	RETURN CURRVAL(PG_GET_SERIAL_SEQUENCE('page', 'id'));
END;
$BODY$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION page_move(child integer, parent integer) RETURNS void AS 
$BODY$
BEGIN
	UPDATE page set parent_id = parent where id = child;
END;
$BODY$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION page_delete(page_id integer) RETURNS void AS 
$BODY$
BEGIN
	DELETE FROM page where id = page_id;
END;
$BODY$ LANGUAGE plpgsql;

-- Test
SELECT page_add('grand-child of test page', page_add('child of test page', page_add('test page', NULL)));
SELECT page_move(3, 1);
SELECT page_delete(2);
SELECT * FROM page;