On Wed, Oct 3, 2012 at 8:15 AM, Léon Melis <leon@leonmelis.nl> wrote:
> 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!
back in the old days, that is, pre-9.0 hstore (the release of which
was an absolute game changer for trigger functions), I used to manage
the the record textually: the trigger function would cast new/old etc
to text and stick it in the audit table that way. If I needed to
access specific fields I would cast it back to the record if
necessary. If the table structure changed, I'd be rewriting the audit
table if there weren't too many records and keeping the old structure
around as a composite type if there were. If I was lazy (I am), I
might just leave it as text and deal with it that way -- textually
parsing (a robust parser for that duplicates record_in() is alot of
work but a simple parser covering common cases is pretty doable) the
record or just plain eyeballing it if I had to go back and look at
something.
Really though, upgrading the database makes the most sense IMO. 8.4
indicates that your customer is probably sticking with the rhel stock
packaging -- typically the workaround there is to just forcefully
argue with the IT dept until they give up and let you run with the
pgdg packaging which is quite excellent. Rumor has it that rhel 7
will standardize on 9.2 which is nice. Anyways, good luck!
merlin
