hostory tables with a generic function?

From: Raphael Bauduin <raphael(dot)bauduin(at)be(dot)easynet(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: hostory tables with a generic function?
Date: 2004-08-10 09:14:03
Message-ID: 411891DB.4090101@be.easynet.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm looking at the logging aof a database we'll put in production soon.
I've seen some posts on this list about history tables, like mentioned in
http://www-106.ibm.com/developerworks/web/library/wa-dbdsgn2.html .
I think I'll go that way too, but I still have some questions on the approach:

- is it possible to write only one function used for all logging triggers?
As illustrated in http://www.varlena.com/varlena/GeneralBits/57.php , it is possible to use
one function for tracking last update times for all tables:

CREATE OR REPLACE FUNCTION setmodtime() RETURNS TRIGGER AS '
BEGIN
NEW.modified_timestamp = now();
RETURN NEW;
END
' LANGUAGE 'plpgsql';

Is it possible to create only one function to insert rows in the corresponding
history table? the name of the history table can be constructed from the "original" table.
and I guess all fields of the table can be retrieved from the db's metadata.
Would that be feasible, and more importantly, would it be usable?

-Another question I have is for those who use this approach: How often o you have to flush those tables
and when you flush the tables, where do you put the flushed data? In another database on another server
or on tape?

Thanks.

Raph

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2004-08-10 09:17:00 Re: PostgreSQL 8.0 Feature List?
Previous Message Peter Eisentraut 2004-08-10 08:41:40 Using connection after fork