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
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 |