1 |
-- |
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'; |