/* To get the namedplace trigger, just search and replace "plantname" with
"namedplace". */

----- Trigger function

-- Function: plantname_update_ancestors()

-- DROP FUNCTION plantname_update_ancestors();

CREATE OR REPLACE FUNCTION plantname_update_ancestors()
  RETURNS trigger AS
$BODY$
BEGIN
    -- Delete existing ancestors
    DELETE FROM plantname_ancestor WHERE plantname_id = new.plantname_id;
    
    IF new.parent_id IS NOT NULL THEN
        -- Copy parent's ancestors to this node's ancestors
        INSERT
        INTO plantname_ancestor
        (plantname_id, ancestor_id)
        SELECT
            new.plantname_id, ancestor_id
        FROM plantname_ancestor
        WHERE plantname_id = new.parent_id
        ;
    END IF;
    
    -- Add "ancestor" for this node
    /* This is useful for queries, because you don't have to separately test if
    the leaf node is the one you're looking for, in addition to that leaf node's
    ancestors. */
    INSERT
    INTO plantname_ancestor
    (plantname_id, ancestor_id)
    VALUES (new.plantname_id, new.plantname_id)
    ;
    
    -- Tell immediate children to update their ancestors lists, which will
    -- recursively tell all descendants
    UPDATE plantname
    SET plantname_id = plantname_id -- need at least one SET statement
    WHERE parent_id = new.plantname_id -- the plantname_id passed as param
    ;
    
    /* Note: We don't need an ON DELETE trigger to update the descendants'
    ancestors when a node is deleted, because the plantname.plantname_parent_id
    foreign key is set to ON DELETE CASCADE, which just removes all the
    descendants anyway. */
    
    RETURN new;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION plantname_update_ancestors()
  OWNER TO bien;

----- Ancestors table

-- Table: plantname_ancestor

-- DROP TABLE plantname_ancestor;

CREATE TABLE plantname_ancestor
(
  plantname_id integer NOT NULL,
  ancestor_id integer NOT NULL,
  CONSTRAINT plantname_ancestor_pkey PRIMARY KEY (plantname_id , ancestor_id ),
  CONSTRAINT plantname_ancestor_ancestor_id FOREIGN KEY (ancestor_id)
      REFERENCES plantname (plantname_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT plantname_ancestor_plantname_id FOREIGN KEY (plantname_id)
      REFERENCES plantname (plantname_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
ALTER TABLE plantname_ancestor
  OWNER TO bien;

-- Index: fki_plantname_ancestor_ancestor_id

-- DROP INDEX fki_plantname_ancestor_ancestor_id;

CREATE INDEX fki_plantname_ancestor_ancestor_id
  ON plantname_ancestor
  USING btree
  (ancestor_id );

-- Index: fki_plantname_ancestor_plantname_id

-- DROP INDEX fki_plantname_ancestor_plantname_id;

CREATE INDEX fki_plantname_ancestor_plantname_id
  ON plantname_ancestor
  USING btree
  (plantname_id );

----- Trigger

-- Trigger: plantname_update_ancestors on plantname

-- DROP TRIGGER plantname_update_ancestors ON plantname;

CREATE TRIGGER plantname_update_ancestors
  AFTER INSERT OR UPDATE
  ON plantname
  FOR EACH ROW
  EXECUTE PROCEDURE plantname_update_ancestors();