Re: Is this possible in a trigger?

On Tuesday 06 May 2008 20:10:50 Klint Gore wrote:
> Fernando wrote:
> > I want to keep a history of changes on a field in a table.  This will
> > be the case in multiple tables.
> >
> > Can I create a trigger that loops the OLD and NEW values and compares
> > the values and if they are different creates a change string as follows:
> >
> > e.g;
> >
> > FOR EACH field IN NEW
> >     IF field.value <> OLD.field.name THEN
> >        changes := changes
> >
> >             || field.name
> >             || ' was: '
> >             || OLD.field.value
> >             || ' now is: '
> >             || field.value
> >             || '\n\r';
> >
> >     END IF
> > END FOR;
> >
> > Your help is really appreciated.
>
> You can't in plpgsql.  It doesn't have the equivalent of a walkable
> fields collection.  Its possible in some other procedure languages (I've
> seen it done in C).
>

I did it once by setting up the function to accept the tablename and ctid of
the row involved, and then grabbing the info from the system tables.
Certainly easier to do it in plperl though.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL