This is easy with plpython. We do something similar. Kerri
On Tue, May 6, 2008 at 6:10 PM, Klint Gore <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Yuma Educational Computer Consortium Compass Development Team Kerri Reno [EMAIL PROTECTED] (928) 502-4240 .·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.
