I am trying to write a trigger function in PostgreSQL 8.2 that will dynamically use TG_TABLE_NAME to generate and execute a SQL statement. I can find all kinds of examples for later versions of PostgreSQL, but I am stuck on 8.2 because of some requirements. Here is my function as it stands which 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;
The way this is currently written, I would have to write a separate trigger function for every table. I would like to use TG_TABLE_NAME to dynamically build my INSERT statement and just prefix it with 'cdc_' since all of the tables follow the same naming convention. Then I can have every trigger for every table call just one function.
See Question&Answers more detail:os