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

----- Trigger function

-- Function: taxonconcept_update_ancestors()

-- DROP FUNCTION taxonconcept_update_ancestors();

CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors()

  RETURNS trigger AS

$BODY$

BEGIN

    -- Delete existing ancestors

    DELETE FROM taxonconcept_ancestor

    WHERE taxonconcept_id = new.taxonconcept_id;

    IF new.parent_id IS NOT NULL THEN

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

        INSERT

        INTO taxonconcept_ancestor

        (taxonconcept_id, ancestor_id)

        SELECT

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

    INTO taxonconcept_ancestor

    (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

    UPDATE taxonconcept

    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

    AND taxonconcept_id != new.taxonconcept_id -- avoid infinite recursion

    ;

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

    ancestors when a node is deleted, because the taxonconcept.parent_id foreign

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

    RETURN new;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION taxonconcept_update_ancestors()

  OWNER TO bien;

----- Ancestors table

-- Table: taxonconcept_ancestor

-- DROP TABLE taxonconcept_ancestor;

CREATE TABLE taxonconcept_ancestor

(

  taxonconcept_id integer NOT NULL,

  ancestor_id integer NOT NULL,

  CONSTRAINT taxonconcept_ancestor_pkey PRIMARY KEY (taxonconcept_id , ancestor_id ),

  CONSTRAINT taxonconcept_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id)

      REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT taxonconcept_ancestor_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id)

      REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE

)

WITH (

  OIDS=FALSE

);

ALTER TABLE taxonconcept_ancestor

  OWNER TO bien;

----- Trigger

-- Trigger: taxonconcept_update_ancestors on taxonconcept

-- DROP TRIGGER taxonconcept_update_ancestors ON taxonconcept;

CREATE TRIGGER taxonconcept_update_ancestors

  AFTER INSERT OR UPDATE

  ON taxonconcept

  FOR EACH ROW

  EXECUTE PROCEDURE taxonconcept_update_ancestors();