Hi.
I have the following schema (question at bottom):
==============================
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
companycomp 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 FUNCTIONindex_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 functionupdate_company_fts_tf() returns TRIGGER AS $$ declare
v_company_idINTEGER; BEGIN v_company_id := NEW.id; perform update_company_fts
(v_company_id);RETURN NULL; END; $$ LANGUAGE plpgsql; -- General cleanup
functions for constraint triggersCREATE 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 FUNCTIONtrigger_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 TRIGGERtrigger_2 AFTER INSERT OR UPDATE of name, duns_number,
parent_idON company -- NOT DEFERRED FOR EACH ROW WHEN (NEW.t_updated IS NULL)
EXECUTE PROCEDUREtrigger_function_set_updated(); CREATE CONSTRAINT TRIGGER
trigger_3AFTER 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
FROMcompany 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 ofparent_id ON company DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN
(NEW.t_updated IS NULL) EXECUTE PROCEDURE company_parent_no_cycle();
==============================
What I'm after is to have 2 "logical constraint-triggers" perform logic only
once (each) on the "company"-table.
To make constraint-triggers fire only once (in PostgreSQL) a common method is
to have a schema with 3 triggers, and a "magic" t_updated column, and they must
be named so they (the triggers, not the trigger-functions) are fired in lexical
order (alphabetically). And it's important that the 2nd. trigger (here
"trigger_2") is NOT deferred.
In my schema above I have 2 "logical chuchks" which each perform some stuff
and shall only do it once per row at commit-time.
The first "main" trigger-function is update_company_fts_tf() and it updates a
column (fts_all) of type tsvector. This is done in a trigger so that it may add
stuff (customer-number etc.) from other tables as needed (which is not possible
with PG-12's new STORED-columns).
The second "main" trigger-function is company_parent_no_cycle() and assures
there are no parent/child-cycles.
Question:
1. I have "re-used" trigger_2 and trigger_3, and trigger_2's "...OR UPDATE
OF"-list is the sum of all columns updated(used) in the 2 main-triggers, that
is "name", "duns_number" and parent_id. trigger_3 only checks t_updated.
Is this correct usage, can I assume this will work correctly?
2. If I need a 3rd "logical trigger", is it enough to add another trigger
named accordingly, for instance "trigger_1_someotherstuff", and add it's column
to the "UPDATE OF"-list of "trigger_2" (it it uses a column not already listed
there)?
3. Is there some easier way to do this?
Is it clear what I'm asking about? :-)
Thanks.
--
Andreas Joseph Krogh
