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

references to matched_concept_id. */

----- Functions

-- Function: taxonconcept_update_ancestors(taxonconcept, integer)

-- DROP FUNCTION taxonconcept_update_ancestors(taxonconcept, integer);

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

  RETURNS void AS

DECLARE

    -- Use matched_concept_id's ancestors instead if available

    parent_id_ taxonconcept.taxonconcept_id%TYPE :=

        COALESCE(new.matched_concept_id, new.parent_id);

    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;

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

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION taxonconcept_update_ancestors(taxonconcept, integer)

  OWNER TO bien;

-- Function: taxonconcept_update_ancestors_on_insert()

-- DROP FUNCTION taxonconcept_update_ancestors_on_insert();

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

    PERFORM taxonconcept_update_ancestors(new);

    RETURN new;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION taxonconcept_update_ancestors_on_insert()

  OWNER TO bien;

-- Function: taxonconcept_update_ancestors_on_update()

-- DROP FUNCTION taxonconcept_update_ancestors_on_update();

CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors_on_update()

  RETURNS trigger AS

$BODY$

BEGIN

    PERFORM taxonconcept_update_ancestors(new,

        COALESCE(old.matched_concept_id, old.parent_id));

ALTER FUNCTION taxonconcept_update_ancestors_on_update()

COMMENT ON TABLE taxonconcept_ancestor

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

-- Index: taxonconcept_ancestor_descendants

-- DROP INDEX taxonconcept_ancestor_descendants;

CREATE INDEX taxonconcept_ancestor_descendants

  ON taxonconcept_ancestor

  USING btree

  (ancestor_id , taxonconcept_id );

----- Triggers

-- Trigger: taxonconcept_update_ancestors_on_insert on taxonconcept

-- DROP TRIGGER taxonconcept_update_ancestors_on_insert ON taxonconcept;

CREATE TRIGGER taxonconcept_update_ancestors_on_insert

  AFTER INSERT

  EXECUTE PROCEDURE taxonconcept_update_ancestors_on_insert();

-- Trigger: taxonconcept_update_ancestors_on_update on taxonconcept

-- DROP TRIGGER taxonconcept_update_ancestors_on_update ON taxonconcept;

CREATE TRIGGER taxonconcept_update_ancestors_on_update

  AFTER UPDATE

  ON taxonconcept

  FOR EACH ROW

  EXECUTE PROCEDURE taxonconcept_update_ancestors_on_update();