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_W开发者_如何学运维HEN = '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.
I was looking for the exact same thing a couple of years back. One trigger function to rule them all! I asked on usenet lists, tried various approaches, to no avail. The consensus on the matter was this could not be done. A shortcoming of PostgreSQL 8.3 or older.
Since PostgreSQL 8.4 you can just:
EXECUTE 'INSERT INTO ' || TG_RELID::regclass::text || ' SELECT ($1).*'
USING NEW;
With pg 8.2 you have a problem:
- cannot dynamically access columns of
NEW
/OLD
. You need to know column names at the time of writing the trigger function. NEW
/OLD
are not visible insideEXECUTE
.EXECUTE .. USING
not born yet.
There is a trick, however.
Every table name in the system can serve as composite type of the same name. Therefore you can create a function that takes NEW
/ OLD
as parameter and execute that. You can dynamically create and destroy that function on every 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 have to be treated like user input. Use quote_ident()
to defend against SQL injection.
However, this way you create and drop a function for every single trigger event. Quite an overhead, I would not go for that. You will have to vacuum some catalog tables a lot.
Middle ground
PostgreSQL supports function overloading. Therefore, one function per table of the same base name (but different parameter type) can coexist. You could take the middle ground and dramatically reduce the noise by creating f_cdc(..)
once per table at the same time you create the trigger. That's one tiny function per table. You have to observe changes of table definitions, but tables shouldn't change that often. Remove CREATE
and DROP FUNCTION
from the trigger function, arriving at a small, fast and elegant trigger.
I could see myself doing that in pg 8.2. Except that I cannot see myself doing anything in pg 8.2 anymore. It has reached end of life in December 2011. Maybe you can upgrade somehow after all.
I also asked a similar question a couple of years ago.
Have a look at that question and see if it gives you any useful ideas:
Inserting NEW.* from a generic trigger using EXECUTE in PL/pgsql
精彩评论