Re: history tables with only one function?

Raphael Bauduin wrote:
>
> 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 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?
>

I found a solution to this one, thanks to a post of Tom Lane on the postgres-novice mailing list:

CREATE FUNCTION "update_log"(text) RETURNS trigger AS '
BEGIN
insert into $1_log select new.*,''UPDATE'';
return new;
END;
' LANGUAGE 'plpgsql';

I can then create a trigger and pass the table name as argument (does the function know
which table fired the trigger?):

CREATE TRIGGER "customers_update_log_t" after UPDATE on "customers" for each row execute procedure "customers_update_log"('customers');

This creates entries in the customers_log table each time I update a customer.

I'll see if I use the same function for the creation log or not. For the delete log, I'll have to use another function anyway,
as new is not defined for a deletion IIRC.

Still interested in tips on this technique ;-)

Raph

>
> -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
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org