/* To get the place trigger, just search and replace taxonconcept with place. */

$BODY$

BEGIN

    WHERE taxonconcept_id = new.taxonconcept_id;

        -- Copy parent's ancestors to this node's ancestors

        INSERT

        (taxonconcept_id, ancestor_id)

        FROM taxonconcept_ancestor

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

    (taxonconcept_id, ancestor_id)

    VALUES (new.taxonconcept_id, new.taxonconcept_id)

    -- Tell immediate children to update their ancestors lists, which will

    -- recursively tell all descendants

    SET taxonconcept_id = taxonconcept_id -- need at least one SET statement

    -- Add COALESCE() to enable using taxonconcept_unique index for lookup

    WHERE COALESCE(parent_id, 2147483647) = new.taxonconcept_id

    /* Note: We don't need an ON DELETE trigger to update the descendants'

    key is ON DELETE CASCADE, which just removes all the descendants anyway. */

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

----- Ancestors table

  CONSTRAINT taxonconcept_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id)

      REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE

      REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE

)

WITH (

  OIDS=FALSE

);

----- Trigger