triggers: dynamic references to fields in NEW and OLD?

Поиск
Список
Период
Сортировка
От Vance Maverick
Тема triggers: dynamic references to fields in NEW and OLD?
Дата
Msg-id DAA9CBC6D4A7584ABA0B6BEA7EC6FC0BE1A195@hq-exch01.corp.pgp.com
обсуждение исходный текст
Ответы Re: triggers: dynamic references to fields in NEW and OLD?
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: PostgreSQL executing my function too many times during query
Следующее
От: "D. Dante Lorenso"
Дата:
Сообщение: Re: PostgreSQL executing my function too many times during query