----- 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.plantname_id -- set to itself

    WHERE parent_id = 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();