Re: [GENERAL] Implementing hierarchy
От | Rob Walker |
---|---|
Тема | Re: [GENERAL] Implementing hierarchy |
Дата | |
Msg-id | 019b01beadcf$98625810$030110ac@maple обсуждение исходный текст |
Ответ на | Implementing hierarchy (Mike Frisch <mfrisch@saturn.tlug.org>) |
Список | pgsql-general |
> I am trying to write code to access a product catalog (more as a learning > exercise than anything else) and need to implement some sort of searchable > hierarcy. For example: > > Computer Hardware (toplevel) > Hard Drives > Internal > SCSI > Fast SCSI > Wide SCSI > SCA > > Assuming these 'categories' are all in the same table as follows: > > prkey (primary key) > descr varchar > parent (for subcategories, toplevel parent is 0) I don't know if there is a 'right' way to do this, but I have done something similar having an extra table that contains a tuple listing (node, ancestor) pairs. This is kept in sync with the main table using a couple of triggers. The code is at the end A sequence is used for the primary key in the main table, and the hierarchy is then implicit since you can't create a child before the parent (at least my application doesn't let you move an existing child to another parent). Rob --- CREATE TABLE places ( id INT4 DEFAULT NEXTVAL('places_seq') PRIMARY KEY, name TEXT NOT NULL, parent INT4 DEFAULT 0 ); CREATE TABLE places_tree ( place INT4, ancestor INT4, PRIMARY KEY (place, ancestor) ); CREATE FUNCTION explode_place () RETURNS OPAQUE AS ' DECLARE row places_tree%ROWTYPE; BEGIN FOR row IN SELECT * FROM places_tree WHERE place = NEW.parent LOOP INSERT INTO places_tree VALUES (NEW.id, row.ancestor); END LOOP; IF NEW.parent <> 0 THEN INSERT INTO places_tree VALUES (NEW.id, NEW.parent); END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE FUNCTION implode_place () RETURNS OPAQUE AS ' DECLARE row places_tree%ROWTYPE; BEGIN DELETE FROM places_tree WHERE place = OLD.id; FOR row IN SELECT * FROM places_tree WHERE ancestor = OLD.id LOOP DELETE FROM places WHERE id = row.place; END LOOP; RETURN OLD; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER explode_place_trigger AFTER INSERT ON places FOR EACH ROW EXECUTE PROCEDURE explode_place(); CREATE TRIGGER implode_place_trigger BEFORE DELETE ON places FOR EACH ROW EXECUTE PROCEDURE implode_place();
В списке pgsql-general по дате отправления: