triggers: dynamic references to fields in NEW and OLD?

From: "Vance Maverick" <vmaverick(at)pgp(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: triggers: dynamic references to fields in NEW and OLD?
Date: 2008-05-15 22:43:49
Message-ID: DAA9CBC6D4A7584ABA0B6BEA7EC6FC0BE1A195@hq-exch01.corp.pgp.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message D. Dante Lorenso 2008-05-15 22:51:59 PostgreSQL executing my function too many times during query
Previous Message Justin 2008-05-15 20:57:03 Re: Need for help!