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

CREATE FUNCTION taxonlabel_2_set_canon_label_id_on_insert() RETURNS trigger

    new.canon_label_id = new.taxonlabel_id; -- make self-reference

    RETURN new;

END;

$$;

--

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

--

CREATE FUNCTION taxonlabel_2_set_canon_label_id_on_update() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    IF new.matched_label_id IS DISTINCT FROM old.matched_label_id THEN

        IF new.matched_label_id IS NOT NULL THEN

            IF new.matched_label_id = new.taxonlabel_id THEN -- self-reference

                new.canon_label_id = new.taxonlabel_id; -- make self-reference

            ELSE -- propagate from matched label

                new.canon_label_id = (

                    SELECT canon_label_id

                    FROM taxonlabel

                    WHERE taxonlabel_id = new.matched_label_id

                );

            END IF;

            -- Update canon_label_id on labels that resolve to this label

            UPDATE taxonlabel

            SET canon_label_id = new.canon_label_id

            WHERE matched_label_id = new.taxonlabel_id

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

            ;

        ELSE -- no matched taxonlabel

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

CREATE TRIGGER taxonlabel_2_set_canon_label_id_on_insert BEFORE INSERT ON taxonlabel FOR EACH ROW EXECUTE PROCEDURE taxonlabel_2_set_canon_label_id_on_insert();

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

--

CREATE TRIGGER taxonlabel_2_set_canon_label_id_on_update BEFORE UPDATE ON taxonlabel FOR EACH ROW EXECUTE PROCEDURE taxonlabel_2_set_canon_label_id_on_update();

--