On May 6, 11:05*pm, ferna...@ggtours.ca (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.
>
> Thank you.

in plpgsql you could

select new into textVar;

and then do acrobatics with the text value of that record... or
converting the text value into a known table record type with EXECUTE
'select ' || quote_literal(textVar) || '::tableRecord' INTO
tableRecordVar statement. But the field names are to be extracted from
the catalog anyway.

Or use plperl or plpython