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