/* To get the place trigger, replace taxonconcept with place and remove

references to matched_concept_id. */

----- Functions

CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors(new taxonconcept, old_parent_id integer DEFAULT NULL::integer)

  RETURNS void AS

$BODY$

DECLARE

    -- Use matched_concept_id's ancestors instead if available

    parent_id_ taxonconcept.taxonconcept_id%TYPE := COALESCE(

        NULLIF(new.matched_concept_id, new.taxonconcept_id), new.parent_id);

BEGIN

    IF parent_id_ IS DISTINCT FROM old_parent_id THEN

        DECLARE

            -- These include the parent itself

            old_ancestors integer[] := (

                SELECT array_agg(ancestor_id) FROM taxonconcept_ancestor

                WHERE taxonconcept_id = old_parent_id

            );

            new_ancestors integer[] := (

                SELECT array_agg(ancestor_id) FROM taxonconcept_ancestor

                WHERE taxonconcept_id = parent_id_

            );

            descendant_id integer;

        BEGIN

            FOR descendant_id IN -- also includes self

                SELECT taxonconcept_id

                FROM taxonconcept_ancestor

                WHERE ancestor_id = new.taxonconcept_id

            LOOP

                -- Delete old parent's ancestors

                DELETE FROM taxonconcept_ancestor

                WHERE taxonconcept_id = descendant_id

                AND ancestor_id = ANY (old_ancestors)

                ;

                -- Add new parent's ancestors

                INSERT INTO taxonconcept_ancestor

                (taxonconcept_id, ancestor_id)

                SELECT descendant_id, unnest(new_ancestors)

                ;

            END LOOP;

        END;

    END IF;

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

    ancestors when a node is deleted, because the

    taxonconcept_ancestor.ancestor_id foreign key is ON DELETE CASCADE. */

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors_on_insert()

  RETURNS trigger AS

$BODY$

BEGIN

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

    ;

    PERFORM taxonconcept_update_ancestors(new);

    RETURN new;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors_on_update()

  RETURNS trigger AS

$BODY$

BEGIN

    PERFORM taxonconcept_update_ancestors(new, COALESCE(

        NULLIF(old.matched_concept_id, old.taxonconcept_id), old.parent_id));

    RETURN new;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

----- Ancestors table

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

);

COMMENT ON TABLE taxonconcept_ancestor

  IS 'Stores the accepted ancestors of a taxonconcept. Auto-populated, so should not be manually modified.';

CREATE INDEX taxonconcept_ancestor_descendants

  ON taxonconcept_ancestor

  USING btree

  (ancestor_id , taxonconcept_id );

----- Triggers

CREATE TRIGGER taxonconcept_update_ancestors_on_insert

  AFTER INSERT

  ON taxonconcept

  FOR EACH ROW

  EXECUTE PROCEDURE taxonconcept_update_ancestors_on_insert();

CREATE TRIGGER taxonconcept_update_ancestors_on_update

  AFTER UPDATE

  ON taxonconcept

  FOR EACH ROW

  EXECUTE PROCEDURE taxonconcept_update_ancestors_on_update();