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

From: Léon Melis <leon(at)leonmelis(dot)nl>
To: pgsql-general(at)postgresql(dot)org
Subject: [PL/PGSQL] column name substitution in PG8.4
Date: 2012-10-03 13:15:55
Message-ID: CAFu3rDpb8kmoD1HNxG3wuSF1agFh=UCC0UGFiGJ4=XmaqRVK0A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2012-10-03 13:17:25 Re: pros and cons of two security models
Previous Message Andreas Kretschmer 2012-10-03 13:15:11 Re: Indexing JSON type