Having more than one constraint trigger on a table - Mailing list pgsql-general

CREATE TABLE company(id SERIAL PRIMARY KEY, parent_id INTEGER REFERENCES company(id) DEFERRABLE INITIALLY DEFERRED , name VARCHAR NOT NULL, duns_number VARCHAR, fts_all tsvector, t_updated BOOLEAN);

CREATE or replace FUNCTION update_company_fts(p_company_id integer) RETURNS VOID AS
$$
BEGIN
    UPDATE company comp   SET fts_all = to_tsvector('simple'
        , comp.name             || ' ' || coalesce(comp.duns_number, '')       )   WHERE comp.id = p_company_id;
   raise notice 'Running update of %', p_company_id;
END;
$$ LANGUAGE plpgsql;

-- re-index all:
CREATE OR REPLACE FUNCTION index_company() RETURNS VOID AS
$$
DECLARE
    v_company_id INTEGER;
begin
    FOR v_company_id IN (SELECT id FROM company)       LOOP
            perform update_company_fts(v_company_id);       END LOOP;
END;
$$ LANGUAGE plpgsql;

create or replace function update_company_fts_tf() returns TRIGGER AS
$$
declare
    v_company_id INTEGER;
BEGIN
    v_company_id := NEW.id;   perform update_company_fts(v_company_id);   RETURN NULL;
END;
$$ LANGUAGE plpgsql;


-- General cleanup functions for constraint triggers
CREATE OR REPLACE FUNCTION trigger_function_set_updated() returns TRIGGER AS
$$
BEGIN
    update company set t_updated = TRUE WHERE id = NEW.id;   RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION trigger_function_clear_updated() returns TRIGGER AS
$$
BEGIN
    update company set t_updated = NULL WHERE id = NEW.id;   RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trigger_1_update_fts   AFTER INSERT OR UPDATE of name, duns_number   ON company DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    WHEN (NEW.t_updated IS NULL)
EXECUTE PROCEDURE update_company_fts_tf();

CREATE CONSTRAINT TRIGGER trigger_2   AFTER INSERT OR UPDATE of name, duns_number, parent_id   ON company -- NOT DEFERRED
    FOR EACH ROW
    WHEN (NEW.t_updated IS NULL)
EXECUTE PROCEDURE trigger_function_set_updated();

CREATE CONSTRAINT TRIGGER trigger_3   AFTER INSERT OR UPDATE OF t_updated   ON company DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    WHEN (NEW.t_updated)
EXECUTE PROCEDURE trigger_function_clear_updated();

CREATE OR REPLACE FUNCTION company_parent_no_cycle() returns TRIGGER AS
$$
BEGIN
    IF (WITH recursive tr (id, parent_id, all_ids, cycle) AS (       SELECT id, parent_id, ARRAY [id], false
        FROM company tr       WHERE id = NEW.id
        UNION ALL
        SELECT t.id, t.parent_id, all_ids || t.id, t.id = ANY (all_ids)       FROM company t                JOIN tr ON t.parent_id = tr.id AND NOT cycle)       SELECT count(*)       FROM tr       where cycle = true) > 0 THEN
        RAISE EXCEPTION 'Cannot have cyclic parent relations for company'
            USING SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, CONSTRAINT = TG_NAME               , ERRCODE = '23514'/*check_violation*/, COLUMN = 'parent_id';   END IF;   RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trigger_1_check_nocycle   AFTER INSERT OR UPDATE of parent_id   ON company DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    WHEN (NEW.t_updated IS NULL)
EXECUTE PROCEDURE company_parent_no_cycle();