Received: from mail.gmx.net (pop.gmx.net [194.221.183.20]) by postgresql.org (8.11.3/8.11.1) with SMTP id f56F7iE75320 for ; Wed, 6 Jun 2001 11:07:44 -0400 (EDT) (envelope-from magnus@gmx.de) Received: (qmail 18911 invoked by uid 0); 6 Jun 2001 15:07:38 -0000 Received: from p3ee33e7a.dip.t-dialin.net (HELO mws) (62.227.62.122) by mail.gmx.net (mp002-rz3) with SMTP; 6 Jun 2001 15:07:38 -0000 From: Markus Wagner To: pgsql-sql@postgresql.org Subject: audit trail and system catalogs Date: Wed, 6 Jun 2001 17:15:20 +0200 X-Mailer: KMail [version 1.1.99] Content-Type: text/plain; charset="iso-8859-1" MIME-Version: 1.0 Message-Id: <01060617152003.30292@mws> Content-Transfer-Encoding: 8bit X-Archive-Number: 200106/63 X-Sequence-Number: 3276 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