Thread: [PL/PGSQL] column name substitution in PG8.4
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 MelisOn 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
Le mercredi 03 octobre 2012 à 22:15 -0500, Merlin Moncure a écrit : > Really though, upgrading the database makes the most sense IMO. 8.4 > indicates that your customer is probably sticking with the rhel stock > packaging Could be Debian Stable too... -- Vincent Veyron http://marica.fr/ Gestion informatisée des dossiers contentieux et des sinistres assurances pour le service juridique