From: Alex Pilosov <alex@pilosoft.com>
To: Markus Wagner <magnus@gmx.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: audit trail and system catalogs
Date: Tue, 12 Jun 2001 14:06:19 -0400 (EDT)
Message-ID: <Pine.BSO.4.10.10106121400480.9902-100000@spider.pilosoft.com> (raw)
In-Reply-To: <01060616011002.30292@mws>
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)
execute insert 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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: alex@pilosoft.com, magnus@gmx.de
Subject: Re: audit trail and system catalogs
In-Reply-To: <Pine.BSO.4.10.10106121400480.9902-100000@spider.pilosoft.com>
References: <01060616011002.30292@mws> <Pine.BSO.4.10.10106121400480.9902-100000@spider.pilosoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox