Kerri
On Tue, May 6, 2008 at 6:10 PM, Klint Gore <kgore4@xxxxxxxxxx> wrote:
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).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.
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@xxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno@xxxxxxxxxx (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.
