Thread: hostory tables with a generic function?

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

Raphael Bauduin <raphael.bauduin@be.easynet.net> writes:
> - is it possible to write only one function used for all logging triggers?

Yes, but it will be very hard and inefficient (maybe even impossible)
in plpgsql.  plpgsql isn't intended for dynamic field access and you'll
really be fighting the language.

I'd suggest writing in C, perhaps after proofing in pltcl.

There are some sample C triggers in the contrib tree that do things
fairly close to this; you might try adapting one of those.

            regards, tom lane