Re: Is this possible in a trigger?

This is easy with plpython. We do something similar.

Kerri

On Tue, May 6, 2008 at 6:10 PM, Klint Gore <kgore4(at)une(dot)edu(dot)au> 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).
>
> Having said that, you might be able to create new and old temp tables and
> then use the system tables to walk the columns list executing sql to check
> for differences.
>
> something like
>
> create temp table oldblah as select old.*;
> create temp table newblah as select new.*;
> for arecord in
> select columnname
> from pg_??columns??
> join pg_??tables?? on ??columns??.xxx = ??tables??.yyy
> where tablename = oldblah and pg_table_is_visible
> loop
>
> execute 'select old.' || arecord.columname || '::text , new. ' ||
> arecord.columname || '::text' ||
> ' from oldblah old, newblah new ' ||
> ' where oldblah.' || arecord.columnname || ' <>
> newblah.' ||arecord.columnname into oldval,newval;
>
> changes := changes || arecord.columnname || ' was ' || oldval || '
> now ' || newval;
> end loop;
> execute 'drop table oldblah';
> execute 'drop table newblah';
>
> performance could be awful though.
>
> klint.
>
> --
> Klint Gore
> Database Manager
> Sheep CRC
> A.G.B.U.
> University of New England
> Armidale NSW 2350
>
> Ph: 02 6773 3789 Fax: 02 6773 3266
> EMail: kgore4(at)une(dot)edu(dot)au
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno(at)yumaed(dot)org (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.