I have a bunch of tables that are similar in some ways, and I'm about to
put triggers on them. The triggers will all do essentially the same
thing -- the only wrinkle is that the name of the column they operate on
varies from table to table. I'd like to have just one trigger function,
written 'dynamically' so it can take the name of the column as a trigger
parameter (in TG_ARGV). For example, given tables
CREATE TABLE a (aa INT);
CREATE TABLE b (bb INT);
I'd like to be able to write a trigger function foo() such that with
trigger declarations
CREATE TRIGGER a_foo AFTER INSERT OR UPDATE OR DELETE ON a
FOR EACH ROW EXECUTE PROCEDURE foo('aa');
CREATE TRIGGER b_foo AFTER INSERT OR UPDATE OR DELETE ON b
FOR EACH ROW EXECUTE PROCEDURE foo('bb');
the logic in foo() reads columns a.aa or b.bb respectively.
I've tried composing a SQL string including the text 'NEW.aa' or
'NEW.bb' appropriately, and then passing this to EXECUTE. This fails:
ERROR: NEW used in query that is not in a rule
Any suggestions?
If this can't be made to work, I'll probably write a function that
dynamically generates the code with the desired substitutions -- so in
the SQL source I'll have
SELECT create_custom_trigger('a', 'aa');
generating both the trigger function and the trigger.
Vance
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
