history tables with only one function? - Mailing list pgsql-general

From Raphael Bauduin
Subject history tables with only one function?
Date
Msg-id 4118B112.2060403@be.easynet.net
Whole thread Raw
Responses Re: history tables with only one function?
List pgsql-general
Hi,

I'm looking at the logging of 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, and would appreciate any advice on it.

Here are some questions I have:

- 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
tablecan 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 history tables
and when you flush the tables, where do you put the flushed data? In another database on another server or on tape?

-Would it be possible to use the replication of Slony-I and only log in the history tables in the slave database? Or is
somthing similar possible?

Thanks.

Raph

pgsql-general by date:

Previous
From: Kaloyan Iliev Iliev
Date:
Subject: Re: Listing views
Next
From: ra@konvergencia.hu
Date:
Subject: Re: Listing views