drop table items cascade;
drop table topics cascade;
drop table item_topics cascade;
create table items (
	item_id	SERIAL PRIMARY KEY,
	item_name text);
create table topics (
	topic_id	SERIAL PRIMARY KEY,
	topic_name	text,
	parent_id	integer references topics (topic_id) );
create table item_topics (
	item_id	integer,
	topic_id integer,
	FOREIGN KEY (item_id) references items (item_id)
		ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (topic_id) references topics (topic_id)
		ON UPDATE CASCADE ON DELETE CASCADE,
	PRIMARY KEY (item_id, topic_id) );

--
-- Delete of a topic: update both items and child topics
--
-- First, reassign the items of the topic to the topics parent
-- But if there is no parent, raise an error and abort the update
-- The items must be reassigned somewhere else sensibly by hand.
--
-- Then update the children of the topic to now have their
-- grandparent (or NULL) as their parent.
-- 
CREATE OR REPLACE function del_topic()
RETURNS TRIGGER AS '
DECLARE
   r_rec RECORD;
BEGIN
   FOR r_rec IN SELECT item_id, topic_id
                FROM item_topics
                WHERE topic_id = OLD.topic_id LOOP
      IF OLD.parent_id IS NULL THEN
         RAISE EXCEPTION
         ''Cannot delete topic % until its records are reassigned.'',
         OLD.topic_name;
      ELSE
         UPDATE item_topics SET topic_id = OLD.parent_id
         WHERE item_id = r_rec.item_id AND topic_id =  r_rec.topic_id;
      END IF;
   END LOOP;

   UPDATE topics SET parent_id=OLD.parent_id
   WHERE parent_id = OLD.topic_id;
   RETURN OLD;
END;
' language 'plpgsql';

CREATE TRIGGER del_topic BEFORE DELETE ON topics
   FOR EACH ROW EXECUTE PROCEDURE del_topic();


--
-- Load test data
--
\i itt_data.sql

--
-- Create a handy view of item_topics
--
CREATE VIEW it AS
SELECT i.item_id, i.item_name, it.topic_id, t.parent_id, t.topic_name
FROM  
	item_topics it JOIN items i USING (item_id) JOIN topics t USING (topic_id);

SELECT item_name, topic_name FROM it; 


--
-- Create an aggregation of topics to form the tree path for any topic
--
CREATE OR REPLACE FUNCTION get_topic_path( integer )
RETURNS TEXT AS '
DECLARE
	path	text;
	topic RECORD;
BEGIN
	SELECT INTO topic topic_name, parent_id FROM topics WHERE topic_id = $1;
	path := topic.topic_name;
	IF topic.parent_id IS NOT NULL
	THEN
		path := (SELECT get_topic_path(topic.parent_id)) || '', '' || path;
   END IF;
	RETURN path;
END;
' LANGUAGE 'plpgsql';

--
-- Show topic path for each topic
--
select topic_name, get_topic_path( topic_id ) from topics;
	
--
-- Show topic path for each item
--
select item_id, item_name, get_topic_path(topic_id) from it;

--
-- Re-Slice the previous query to be an ordered set of tuples
--
DROP TYPE topic_node CASCADE;
CREATE TYPE topic_node AS (tn_id integer, tn_parent integer);

CREATE or REPLACE FUNCTION get_topic_node( integer )
RETURNS SETOF topic_node AS '
DECLARE
	t topic_node;
	t2 topic_node;
BEGIN
	FOR t IN SELECT topic_id, parent_id 
		FROM topics 
		WHERE topic_id = $1
	LOOP
		IF t.tn_parent IS NOT NULL
		THEN
			FOR t2 IN SELECT * FROM get_topic_node(t.tn_parent) LOOP
				RETURN NEXT t2;
			END LOOP;
		END if;
		RETURN NEXT t;
	END LOOP;
	RETURN t;
END;
' language 'plpgsql';

--
-- Select the parent nodes of a topic.
-- The parameter to get_topic_node must be hardcoded here.
-- The result tuples are ORDERED
--
SELECT t.topic_name AS base_topic, tn_id , t2.topic_name
FROM topics t, get_topic_node(11) g, topics t2
WHERE t.topic_id = 11 AND t2.topic_id = g.tn_id;


--
-- Expand the previous query to work by item.
-- This enables the previous query work w/ a parameter
-- by encasing it in yet another function.
-- It also expands to allow selection of multiple items.
-- Qualify the item in the call to the query using item_path
-- 
DROP TYPE item_path CASCADE;
CREATE TYPE item_path AS (item_id integer, topic_id integer);

CREATE OR REPLACE FUNCTION item_path ()
RETURNS SETOF item_path AS '
DECLARE
	it item_path;
	i record;
	tn topic_node;
BEGIN
	FOR i IN SELECT item_id, topic_id FROM item_topics LOOP
		it.item_id = i.item_id;
		FOR tn IN SELECT 	* FROM get_topic_node ( i.topic_id ) LOOP
			it.topic_id = tn.tn_id;
			RETURN NEXT it;
		END LOOP;
	END LOOP;
	RETURN it;
END;
' LANGUAGE 'plpgsql';

SELECT it.item_id, i.item_name, it.topic_id, t.topic_name
FROM items i, topics t, item_path() it
WHERE it.item_id = i.item_id AND it.topic_id = t.topic_id;