Re: [GENERAL] Implementing hierarchy

> 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();