Re: field incrementing in a PL/pgSQL trigger
От | Pierre-Frédéric Caillaud |
---|---|
Тема | Re: field incrementing in a PL/pgSQL trigger |
Дата | |
Msg-id | opsgc332wdcq72hf@musicbox обсуждение исходный текст |
Ответ на | field incrementing in a PL/pgSQL trigger ("Tim Vadnais" <tvadnais@earthlink.net>) |
Список | pgsql-general |
Create a different trigger function for each table, then each trigger can be customized to know the column names. You can generate the triggers from a little script which queries the system tables to get at the column names. It would spit code like 'IF NEW.fieldname != OLD.fieldname THEN (record modification...) END IF for each field... Less elegent than a general solution, but why not. > Hi, > > My boss wants to add some logging functionality to some of our tables on > update/delete/insert. I need to log who, when, table_name, field name, > original value and new value for each record, but only logging modified > fields, and he wants me to do this wing postgres pgSQL triggers. > > We are given 10 automatically created variables. Some of which I know I > can > use: NEW, OLD, TG_WHEN, TG_OP and TG_RELNAME. I can use these to get > general information for the update, but when the trigger is called, I > don't > know how many fields are in the tables that are being updated. > > My questions are: Is there a way I can dynamically determine the number > of > fields in the row that is being maintained. (a function much like: > PQnfields(const PGresult *); ) > Then I need a way to get the name of the field (using a function much > like: > PQfname(const PGresult *, int); ) > > Using the dynamically generated name I could then walk the NEW and OLD > rows > to compare the values. (e.g. if (NEW.field != OLD.field) do something;); > > Can anyone help me with this? Thank you in advance. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
В списке pgsql-general по дате отправления: