trunk/sql/tree-func.sql

--
-- Delete of a topic: update both items and child topics
--
-- First, reassign the items of the topic to the topics parent
-- But if there is no parent, raise an error and abort the update
-- The items must be reassigned somewhere else sensibly by hand.
--
-- Then update the children of the topic to now have their
-- grandparent (or NULL) as their parent.
-- 
CREATE OR REPLACE function del_topic()
RETURNS TRIGGER AS $$
DECLARE
   r_rec RECORD;
BEGIN
   FOR r_rec IN SELECT item_id, topic_id
                FROM item_topics
                WHERE topic_id = OLD.topic_id LOOP
      IF OLD.parent_id IS NULL THEN
         RAISE EXCEPTION
         'Cannot delete topic % until its records are reassigned.',
         OLD.topic_name;
      ELSE
         UPDATE item_topics SET topic_id = OLD.parent_id
         WHERE item_id = r_rec.item_id AND topic_id =  r_rec.topic_id;
      END IF;
   END LOOP;

   UPDATE topics SET parent_id=OLD.parent_id
   WHERE parent_id = OLD.topic_id;
   RETURN OLD;
END;
$$ language 'plpgsql';

CREATE TRIGGER del_topic BEFORE DELETE ON topics
   FOR EACH ROW EXECUTE PROCEDURE del_topic();


-- show all items and thair topics
CREATE VIEW it AS
SELECT i.id as i_id, i.name as item, it.topic_id, t.parent_id, t.name as topic
FROM    
        item_topics it JOIN items i on i.id = item_id JOIN topics t on t.id = topic_id;

--
-- Create an aggregation of topics to form the tree path for any topic
--
CREATE OR REPLACE FUNCTION get_topic_path( integer )
RETURNS TEXT AS $$
DECLARE
        path    text;
        topic_r RECORD;
BEGIN
        SELECT INTO topic_r name, parent_id FROM topics WHERE topics.id = $1;
        path := topic_r.name;
        IF topic_r.parent_id IS NOT NULL
        THEN
                path := (SELECT get_topic_path(topic_r.parent_id)) || ' >> ' || path;
   END IF;
        RETURN path;
END;
$$ LANGUAGE 'plpgsql';

--
-- Re-Slice the previous query to be an ordered set of tuples
--
DROP TYPE topic_node CASCADE;
CREATE TYPE topic_node AS (tn_id integer, tn_parent integer);

CREATE or REPLACE FUNCTION get_topic_node( integer )
RETURNS SETOF topic_node AS $$
DECLARE
        t       topic_node;
        t2      topic_node;
BEGIN
        FOR t IN SELECT id, parent_id 
                FROM topics 
                WHERE id = $1
        LOOP
                IF t.tn_parent IS NOT NULL
                THEN
                        FOR t2 IN SELECT * FROM get_topic_node(t.tn_parent) LOOP
                                RETURN NEXT t2;
                        END LOOP;
                END if;
                RETURN NEXT t;
        END LOOP;
        RETURN;
END;
$$ language 'plpgsql';


--
-- Expand the previous query to work by item.
-- This enables the previous query work w/ a parameter
-- by encasing it in yet another function.
-- It also expands to allow selection of multiple items.
-- Qualify the item in the call to the query using item_path
-- 
DROP TYPE item_path CASCADE;
CREATE TYPE item_path AS (item_id integer, topic_id integer);

CREATE OR REPLACE FUNCTION item_path ()
RETURNS SETOF item_path AS $$
DECLARE
        it      item_path;
        i_r     record;
        tn      topic_node;
BEGIN
        FOR i_r IN SELECT item_id, topic_id FROM item_topics LOOP
                it.item_id = i_r.item_id;
                FOR tn IN SELECT * FROM get_topic_node ( i_r.topic_id ) LOOP
                        it.topic_id = tn.tn_id;
                        RETURN NEXT it;
                END LOOP;
        END LOOP;
        RETURN;
END;
$$ LANGUAGE 'plpgsql';
1	dpavlin	37	--
2			-- Delete of a topic: update both items and child topics
3			--
4			-- First, reassign the items of the topic to the topics parent
5			-- But if there is no parent, raise an error and abort the update
6			-- The items must be reassigned somewhere else sensibly by hand.
7			--
8			-- Then update the children of the topic to now have their
9			-- grandparent (or NULL) as their parent.
10			--
11			CREATE OR REPLACE function del_topic()
12			RETURNS TRIGGER AS $$
13			DECLARE
14			r_rec RECORD;
15			BEGIN
16			FOR r_rec IN SELECT item_id, topic_id
17			FROM item_topics
18			WHERE topic_id = OLD.topic_id LOOP
19			IF OLD.parent_id IS NULL THEN
20			RAISE EXCEPTION
21			'Cannot delete topic % until its records are reassigned.',
22			OLD.topic_name;
23			ELSE
24			UPDATE item_topics SET topic_id = OLD.parent_id
25			WHERE item_id = r_rec.item_id AND topic_id = r_rec.topic_id;
26			END IF;
27			END LOOP;
28
29			UPDATE topics SET parent_id=OLD.parent_id
30			WHERE parent_id = OLD.topic_id;
31			RETURN OLD;
32			END;
33			$$ language 'plpgsql';
34
35			CREATE TRIGGER del_topic BEFORE DELETE ON topics
36			FOR EACH ROW EXECUTE PROCEDURE del_topic();
37
38
39			-- show all items and thair topics
40			CREATE VIEW it AS
41			SELECT i.id as i_id, i.name as item, it.topic_id, t.parent_id, t.name as topic
42			FROM
43			item_topics it JOIN items i on i.id = item_id JOIN topics t on t.id = topic_id;
44
45			--
46			-- Create an aggregation of topics to form the tree path for any topic
47			--
48			CREATE OR REPLACE FUNCTION get_topic_path( integer )
49			RETURNS TEXT AS $$
50			DECLARE
51			path text;
52			topic_r RECORD;
53			BEGIN
54			SELECT INTO topic_r name, parent_id FROM topics WHERE topics.id = $1;
55			path := topic_r.name;
56			IF topic_r.parent_id IS NOT NULL
57			THEN
58			path := (SELECT get_topic_path(topic_r.parent_id)) \|\| ' >> ' \|\| path;
59			END IF;
60			RETURN path;
61			END;
62			$$ LANGUAGE 'plpgsql';
63
64			--
65			-- Re-Slice the previous query to be an ordered set of tuples
66			--
67			DROP TYPE topic_node CASCADE;
68			CREATE TYPE topic_node AS (tn_id integer, tn_parent integer);
69
70			CREATE or REPLACE FUNCTION get_topic_node( integer )
71			RETURNS SETOF topic_node AS $$
72			DECLARE
73			t topic_node;
74			t2 topic_node;
75			BEGIN
76			FOR t IN SELECT id, parent_id
77			FROM topics
78			WHERE id = $1
79			LOOP
80			IF t.tn_parent IS NOT NULL
81			THEN
82			FOR t2 IN SELECT * FROM get_topic_node(t.tn_parent) LOOP
83			RETURN NEXT t2;
84			END LOOP;
85			END if;
86			RETURN NEXT t;
87			END LOOP;
88			RETURN;
89			END;
90			$$ language 'plpgsql';
91
92
93			--
94			-- Expand the previous query to work by item.
95			-- This enables the previous query work w/ a parameter
96			-- by encasing it in yet another function.
97			-- It also expands to allow selection of multiple items.
98			-- Qualify the item in the call to the query using item_path
99			--
100			DROP TYPE item_path CASCADE;
101			CREATE TYPE item_path AS (item_id integer, topic_id integer);
102
103			CREATE OR REPLACE FUNCTION item_path ()
104			RETURNS SETOF item_path AS $$
105			DECLARE
106			it item_path;
107			i_r record;
108			tn topic_node;
109			BEGIN
110			FOR i_r IN SELECT item_id, topic_id FROM item_topics LOOP
111			it.item_id = i_r.item_id;
112			FOR tn IN SELECT * FROM get_topic_node ( i_r.topic_id ) LOOP
113			it.topic_id = tn.tn_id;
114			RETURN NEXT it;
115			END LOOP;
116			END LOOP;
117			RETURN;
118			END;
119			$$ LANGUAGE 'plpgsql';