----- Trigger function

-- Function: plantname_update_ancestors()

-- DROP FUNCTION plantname_update_ancestors();

CREATE OR REPLACE FUNCTION plantname_update_ancestors()

  RETURNS trigger AS

$BODY$

BEGIN

    IF new.parent_id IS NOT NULL THEN

        -- Delete existing ancestors

        DELETE FROM plantname_ancestor WHERE plantname_id = new.plantname_id;

        -- 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

        ;

        -- 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.

        */

    END IF;

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