git.rot13.org Git - webpac2/blob - sql/tree-func.sql

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