-- Name: taxonconcept_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: taxonconcept_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE taxonconcept_ancestor (

    taxonconcept_id int(11) NOT NULL,

    ancestor_id int(11) NOT NULL

);

--

-- Name: taxonconcept_ancestor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

--

ALTER TABLE taxonconcept_ancestor

    ADD CONSTRAINT taxonconcept_ancestor_pkey PRIMARY KEY (taxonconcept_id, ancestor_id);

--

-- Name: taxonconcept_update_ancestors; Type: TRIGGER; Schema: public; Owner: -

--

--

-- Name: taxonconcept_ancestor_ancestor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE taxonconcept_ancestor

    ADD CONSTRAINT taxonconcept_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

-- Name: taxonconcept_ancestor_taxonconcept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE taxonconcept_ancestor

    ADD CONSTRAINT taxonconcept_ancestor_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

-- Name: taxonconcept_update_ancestors(); Type: FUNCTION; Schema: public; Owner: -

--

CREATE FUNCTION taxonconcept_update_ancestors() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

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;

$$;

--

-- Name: taxonconcept_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE taxonconcept_ancestor (

    taxonconcept_id integer NOT NULL,

    ancestor_id integer NOT NULL

);

--

-- Name: taxonconcept_ancestor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 

--

ALTER TABLE ONLY taxonconcept_ancestor

    ADD CONSTRAINT taxonconcept_ancestor_pkey PRIMARY KEY (taxonconcept_id, ancestor_id);

--

-- Name: taxonconcept_update_ancestors; Type: TRIGGER; Schema: public; Owner: -

--

CREATE TRIGGER taxonconcept_update_ancestors AFTER INSERT OR UPDATE ON taxonconcept FOR EACH ROW EXECUTE PROCEDURE taxonconcept_update_ancestors();

--

-- Name: taxonconcept_ancestor_ancestor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE ONLY taxonconcept_ancestor

    ADD CONSTRAINT taxonconcept_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

--

-- Name: taxonconcept_ancestor_taxonconcept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -

--

ALTER TABLE ONLY taxonconcept_ancestor

    ADD CONSTRAINT taxonconcept_ancestor_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;

--