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

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