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)
>
