Re: Is this possible in a trigger?

On May 6, 11:05 pm, ferna(dot)(dot)(dot)(at)ggtours(dot)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 :(