The conventional solution for this is to have a trigger on update,
and have a history table with the same structure as original table, and
inserting the old unmodified row into history.
IF you really require structure like "field oldvalue newvalue", it doesn't
seem possible in generic way without writing some C code to look at the
fieldlists of the tables and compare them. Alternatively, you may try
using EXECUTE feature of plpgsql and construct/execute queries dynamically
to find out if attribute has changed or not, but that will be much slower
than a C function.
Your plpgsql code would look like
for attr in (list of attrs of the current table) execute into is_diff 'select old.attr <> new.attr' if (is_diff)
executeinsert into audit tablename, attr, old.attr, new.attr
end
(This is pseudocode, I gloss over most things :)
On Wed, 6 Jun 2001, Markus Wagner wrote:
> Hi,
>
> we strongly need to implement an audit trail as a prerequisite for
> clinical trials, that is a functionality which records any change of any
> data item in a database into one single table containing these events.
>
> I found that one could use rules for this, but this would require one rule
> for each attribute of each table ("...ON UPDATE ... WHERE old.attr <>
> new.attr...").
>
> My first question:
> Could one reduce the rule set with rules for tables, e. g. one rule which
> fires whenever *some* attribute of a table changes? Would there be a
> possibility to reference the changed attributes in the action clause?
>
> Assuming that one really needs one rule for each attribute I would like to
> create a script which generates all rules for all attributes of all tables
> for a database.
>
> The problem is, how to get the names of all tables and their attributes? I
> looked into the system tables ("pg_*"), but there were many tables and
> many attributes for *my* tables, and I did not figure out how to
> distinguish my tables and my attributes from the other ones. None of the
> columns in pg_class and pg_attribute seems to give information on wether
> the item is system or user defined.
>
> My second question:
> How can I loop to all of *my* tables and *my* attributes, ignoring system
> tables and system generated attributes within my tables?
>
> Thank you very much for any hint,
>
> Markus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>