2 -- Delete of a topic: update both items and child topics
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.
8 -- Then update the children of the topic to now have their
9 -- grandparent (or NULL) as their parent.
11 CREATE OR REPLACE function del_topic()
16 FOR r_rec IN SELECT item_id, topic_id
18 WHERE topic_id = OLD.topic_id LOOP
19 IF OLD.parent_id IS NULL THEN
21 'Cannot delete topic % until its records are reassigned.',
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;
29 UPDATE topics SET parent_id=OLD.parent_id
30 WHERE parent_id = OLD.topic_id;
33 $$ language 'plpgsql';
35 CREATE TRIGGER del_topic BEFORE DELETE ON topics
36 FOR EACH ROW EXECUTE PROCEDURE del_topic();
39 -- show all items and thair topics
41 SELECT i.id as i_id, i.name as item, it.topic_id, t.parent_id, t.name as topic
43 item_topics it JOIN items i on i.id = item_id JOIN topics t on t.id = topic_id;
46 -- Create an aggregation of topics to form the tree path for any topic
48 CREATE OR REPLACE FUNCTION get_topic_path( integer )
54 SELECT INTO topic_r name, parent_id FROM topics WHERE topics.id = $1;
56 IF topic_r.parent_id IS NOT NULL
58 path := (SELECT get_topic_path(topic_r.parent_id)) || ' >> ' || path;
62 $$ LANGUAGE 'plpgsql';
65 -- Re-Slice the previous query to be an ordered set of tuples
67 DROP TYPE topic_node CASCADE;
68 CREATE TYPE topic_node AS (tn_id integer, tn_parent integer);
70 CREATE or REPLACE FUNCTION get_topic_node( integer )
71 RETURNS SETOF topic_node AS $$
76 FOR t IN SELECT id, parent_id
80 IF t.tn_parent IS NOT NULL
82 FOR t2 IN SELECT * FROM get_topic_node(t.tn_parent) LOOP
90 $$ language 'plpgsql';
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
100 DROP TYPE item_path CASCADE;
101 CREATE TYPE item_path AS (item_id integer, topic_id integer);
103 CREATE OR REPLACE FUNCTION item_path ()
104 RETURNS SETOF item_path AS $$
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;
119 $$ LANGUAGE 'plpgsql';