How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?

Question

I am trying to write a trigger function in PostgreSQL 8.2 that will dynamically use TG_TABLE_NAME to generate and execute an SQL statement. I can find all kinds of examples for later versions of PostgreSQL, but I am stuck on 8.2 due to some requirements. Here is my function as it works, but is hardly dynamic:

CREATE OR REPLACE FUNCTION cdc_TABLENAME_function() RETURNS trigger AS $cdc_function$ DECLARE op cdc_operation_enum; BEGIN op = TG_OP; IF (TG_WHEN = 'BEFORE') THEN IF (TG_OP = 'UPDATE') THEN op = 'UPDATE_BEFORE'; END IF; INSERT INTO cdc_test VALUES (DEFAULT,DEFAULT,op,DEFAULT,DEFAULT,OLD.*); ELSE IF (TG_OP = 'UPDATE') THEN op = 'UPDATE_AFTER'; END IF; INSERT INTO cdc_test VALUES (DEFAULT,DEFAULT,op,DEFAULT,DEFAULT,NEW.*); END IF; IF (TG_OP = 'DELETE') THEN RETURN OLD; ELSE RETURN NEW; END IF; END; 

As it is currently written, I would have to write a separate trigger function for each table. I would like to use TG_TABLE_NAME to dynamically create my INSERT statement and just prefix it with 'cdc_', since all tables follow the same naming convention. Then I can have every trigger for every table call with only one function.

Answer 1

I was looking for the same thing a couple of years ago. One trigger function to control them all! I asked on Usenet lists, tried various approaches, but to no avail. Consensus on this was not possible. Lack of PostgreSQL 8.3 or later.

Starting with PostgreSQL 8.4, you can simply:

 EXECUTE 'INSERT INTO ' || TG_RELID::regclass::text || ' SELECT ($1).*' USING NEW; 

With pg 8.2 you have a problem:

• cannot dynamically access NEW / OLD columns. You must know the column names when writing the trigger function.
• NEW / OLD not visible inside EXECUTE .
• EXECUTE .. USING has not been born yet.

However, there is a trick.

Each table name in the system can be a composite type with the same name. Therefore, you can create a function that takes NEW / OLD as a parameter and executes it. You can dynamically create and destroy this function for each trigger event:

Trigger function:

 CREATE OR REPLACE FUNCTION trg_cdc() RETURNS trigger AS $func$ DECLARE op text := TG_OP || '_' || TG_WHEN; tbl text := quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME); cdc_tbl text := quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident('cdc_' || TG_TABLE_NAME); BEGIN EXECUTE 'CREATE FUNCTION f_cdc(n ' || tbl || ', op text) RETURNS void AS $x$ BEGIN INSERT INTO ' || cdc_tbl || ' SELECT op, (n).*; END $x$ LANGUAGE plpgsql'; CASE TG_OP WHEN 'INSERT', 'UPDATE' THEN PERFORM f_cdc(NEW, op); WHEN 'DELETE' THEN PERFORM f_cdc(OLD, op); ELSE RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP; END CASE; EXECUTE 'DROP FUNCTION f_cdc(' || tbl || ', text)'; IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF; END $func$ LANGUAGE plpgsql; 

Trigger:

 CREATE TRIGGER cdc BEFORE INSERT OR UPDATE OR DELETE ON my_tbl FOR EACH ROW EXECUTE PROCEDURE trg_cdc(); 

Table names should be treated as user input. Use quote_ident() to protect against SQL injection.

However , this way you create and discard a function for each trigger event. Pretty overhead, I would not go for it. You will have to vacuum some tables in directories.

Middle earth

PostgreSQL supports function overloading . Thus, one function from a table with the same base name (but with a different type of parameter) can coexist. You can take a middle position and significantly reduce noise by creating f_cdc(..) once at the table at the same time as creating the trigger. This is one tiny function for a table. You should observe changes to table definitions, but tables should not change frequently. Remove CREATE and DROP FUNCTION from the trigger function to get a small, fast and elegant trigger.

I could see how I do it in section 8.2. Except that I no longer see that I am doing anything in clause 8.2. In December 2011, he reached the end of his life . Maybe you can still upgrade.

Answer 2

I also asked a similar question a couple of years ago.

Take a look at this question and see if it gives you useful ideas:

Insert NEW. * from a common trigger using EXECUTE in PL / pgsql