| 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'; |
Parent Directory
| 
Revision Log