Re: Is this possible in a trigger?

Thank you for your answer. I guess I better create this history in the
application's data class.

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).
>
> 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.
>