Table changes logging

From: "Didier Gasser-Morlay" <didiergm(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Table changes logging
Date: 2008-01-31 09:07:05
Message-ID: 608b66ce0801310107g3624c274h809229e282a250da@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello

I need to log the changes to a table record in an update trigger. that
is easy to achieve. the problem is that I cannot use a straight
recording of the Old record in a log table as my users want the
changes to be recorded as on record per field change for example

Field amount and due date have been changed today by user U, they
would like to have a table recording

User U, 31St jan 2008 'changed field amount to xxxxx'
User U, 31St jan 2008 'changed field duedate to dd/mm/yyyy'
.....

I could write a trigger checking each individual field and inserting
the right record, but I am wondering id the is a way to loop through a
single record

loop
if new.<fieldname> <> old.<fieldname> then insert into .....

I could then have a generic way of recording changes

I could go to the schema tables and find the field names that way and
then build and execute some dynamic SQL, but I am concerned that this
will be very slow (the table has got 90+ fields

any idea

thanks

Didier

Browse pgsql-novice by date

  From Date Subject
Next Message Michael Lush 2008-01-31 13:16:23 postgres 2 other databases
Previous Message Michael Lush 2008-01-31 08:41:47 Re: Protecting a web app from Postgresql injection