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