For some of my customers I wrote a PL/PGSQL function that stores the difference between an OLD en NEW record when updating a record. This system can be applied as a trigger on the table the customer likes to audit. Because the function can be applied as a trigger on different tables, the function needs to work with dynamic field names.
For PG 9.x I wrote the function like this:
[...]
new_rec = hstore(NEW);
old_rec = hstore(OLD);
FOR col IN SELECT attname FROM pg_attribute WHERE attrelid = TG_RELID AND attstattarget != 0 LOOP
IF new_rec->col IS DISTINCT FROM old_rec->col THEN
INSERT INTO audit (...);
END IF;
END LOOP;
[...]
I use the hstore extension to load the OLD en NEW recordset into an array and then fetch the column names from pg_attribute to iterate through the arrays. This worked just fine for me.
However, I now have a customer using PG8.4 and I they need a similar auditing functionality. The problem is that hstore in PG8.4 does not seem to support creating an array from a record. So I'm searching for a solution to either load an record into an array in PG8.4 or any other method to iterate through a recordset without knowing the layout of the record.
Any suggestion would be highly appreciated!
Regards,
Léon Melis
