From: | Markus Wagner <magnus(at)gmx(dot)de> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | audit trail and system catalogs |
Date: | 2001-06-06 14:01:10 |
Message-ID: | 01060616011002.30292@mws |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gaizka Villate | 2001-06-06 14:48:03 | Sub-SELECT uses un-GROUPed attribute: how to solve. |
Previous Message | Guru Prasad | 2001-06-06 13:22:31 | Large Objects - lo_export |