Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f5CGDsE31776 for ; Tue, 12 Jun 2001 12:13:54 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from mail.gmx.net (pop.gmx.net [194.221.183.20]) by postgresql.org (8.11.3/8.11.1) with SMTP id f56DrXE40217 for ; Wed, 6 Jun 2001 09:53:33 -0400 (EDT) (envelope-from magnus@gmx.de) Received: (qmail 16554 invoked by uid 0); 6 Jun 2001 13:53:27 -0000 Received: from p3ee33d8f.dip.t-dialin.net (HELO mws) (62.227.61.143) by mail.gmx.net (mail07) with SMTP; 6 Jun 2001 13:53:27 -0000 From: Markus Wagner To: pgsql-sql@postgresql.org Subject: audit trail and system catalogs Date: Wed, 6 Jun 2001 16:01:10 +0200 X-Mailer: KMail [version 1.1.99] Content-Type: text/plain; charset="iso-8859-1" MIME-Version: 1.0 Message-Id: <01060616011002.30292@mws> Content-Transfer-Encoding: 8bit X-Archive-Number: 200106/177 X-Sequence-Number: 3405 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