Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group