Search Postgresql Archives

[PL/PGSQL] column name substitution in PG8.4

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux