MUknxNid

1DROP TABLE IF EXISTS page;
2CREATE TABLE IF NOT EXISTS page
3(
4  id serial NOT NULL,
5  title character(64) NOT NULL,
6  parent_id integer
7);
8
9DROP TABLE IF EXISTS page_hierarchy;
10CREATE TABLE IF NOT EXISTS page_hierarchy(
11	id serial NOT NULL,
12	parent_id integer NOT NULL,
13	child_id integer NOT NULL,
14	depth integer NOT NULL
15);
16
17
18CREATE OR REPLACE FUNCTION page_hierarchy_ai() RETURNS TRIGGER AS
19$BODY$
20DECLARE
21BEGIN
22    INSERT INTO page_hierarchy (parent_id, child_id, depth) VALUES (NEW.id, NEW.id, 0);
23    INSERT INTO page_hierarchy (parent_id, child_id, depth)
24	SELECT x.parent_id, NEW.id, x.depth + 1
25	FROM page_hierarchy x
26	WHERE x.child_id = NEW.parent_id;
27    RETURN NEW;
28END;
29$BODY$
30LANGUAGE 'plpgsql';
31DROP TRIGGER IF EXISTS page_hierarchy_ai ON page;
32CREATE TRIGGER page_hierarchy_ai AFTER INSERT ON page
33FOR EACH ROW EXECUTE PROCEDURE page_hierarchy_ai();
34
35
36
37    CREATE OR REPLACE FUNCTION page_hierarchy_bu() RETURNS TRIGGER AS
38    $BODY$
39    DECLARE
40    BEGIN
41        IF NEW.id <> OLD.id THEN
42            RAISE EXCEPTION 'Changing ids is forbidden.';
43        END IF;
44        IF NOT OLD.parent_id IS DISTINCT FROM NEW.parent_id THEN
45            RETURN NEW;
46        END IF;
47        IF NEW.parent_id IS NULL THEN
48            RETURN NEW;
49        END IF;
50        PERFORM 1 FROM page_hierarchy WHERE ( parent_id, child_id ) = ( NEW.id, NEW.parent_id );
51        IF FOUND THEN
52            RAISE EXCEPTION 'Update blocked, because it would create loop in tree.';
53        END IF;
54        RETURN NEW;
55    END;
56    $BODY$
57    LANGUAGE 'plpgsql';
58    DROP TRIGGER IF EXISTS page_hierarchy_bu ON page;
59    CREATE TRIGGER page_hierarchy_bu BEFORE UPDATE ON page
60    FOR EACH ROW EXECUTE PROCEDURE page_hierarchy_bu();
61
62
63
64    CREATE OR REPLACE FUNCTION page_hierarchy_au() RETURNS TRIGGER AS
65    $BODY$
66        DECLARE
67        BEGIN
68            IF NOT OLD.parent_id IS DISTINCT FROM NEW.parent_id THEN
69                RETURN NEW;
70            END IF;
71            IF OLD.parent_id IS NOT NULL THEN
72                DELETE FROM page_hierarchy WHERE id in (
73                    SELECT r2.id FROM page_hierarchy r1
74                    join page_hierarchy r2 on r1.child_id = r2.child_id
75                    WHERE r1.parent_id = NEW.id AND r2.depth > r1.depth
76                );
77            END IF;
78            IF NEW.parent_id IS NOT NULL THEN
79                INSERT INTO page_hierarchy (parent_id, child_id, depth)
80                SELECT r1.parent_id, r2.child_id, r1.depth + r2.depth + 1
81                FROM
82                    page_hierarchy r1,
83                    page_hierarchy r2
84                WHERE
85                    r1.child_id = NEW.parent_id AND
86                    r2.parent_id = NEW.id;
87            END IF;
88            RETURN NEW;
89        END;
90    $BODY$
91    LANGUAGE 'plpgsql';
92    DROP TRIGGER IF EXISTS page_hierarchy_au ON page;
93    CREATE TRIGGER page_hierarchy_au AFTER UPDATE ON page
94    FOR EACH ROW EXECUTE PROCEDURE page_hierarchy_au();
95
96DROP FUNCTION IF EXISTS add_page(character(64), integer);
97CREATE OR REPLACE FUNCTION add_page(title character(64), parent_id integer) RETURNS integer AS 
98$BODY$
99BEGIN
100	INSERT INTO page(title, parent_id) VALUES(title, parent_id);
101	RETURN CURRVAL(PG_GET_SERIAL_SEQUENCE('page', 'id'));
102END;
103$BODY$ LANGUAGE plpgsql;
104
105SELECT add_page('grand-child of test page', add_page('child of test page', add_page('test page', NULL)));
106SELECT * FROM page_hierarchy;