1

I have a trigger that before every update on hundreds of tables it inserts into a table with the same name and the _hv suffix a copy of the record being updated to maintain a kind of historical updates.

So when there is an update in my_example table. The table my_example_hv that has the same fields in the beginning of the table and some extra fields in the end (update timestamps, update user, etc). Using this line it works perfectly:

EXECUTE 'INSERT INTO '|| TG_RELNAME ||'_hv SELECT  ($1).*' USING NEW;

Ok the problem comes when I need to insert a new field to the main table. I can add the new field too to the _hv table but I can't reorder the fields in my postgresql version (8.4). I know it's obsolete but I can't upgrade it right now because I have some old software that needs that version of postgresql.

So what would be the code to insert the record to th _hv table having some field now in different order but with the same column names?

6
  • Erm, why not just list the column names? Commented Mar 24, 2015 at 9:14
  • because I need a trigger that works for the hundreds of tables I've got, so I can avoid having to create hundreds of triggers
    – Egidi
    Commented Mar 24, 2015 at 11:05
  • Listing the column names is probably the only way you're going to get this to work. I'd query metadata to generate column lists or even entire triggers. Commented Mar 24, 2015 at 11:30
  • 1
    If hstore(NEW.*) works with 8.4, you might use that to reconstruct a dynamic insert with column names in any order as hstore keys, and column values as corresponding hstore values. Commented Mar 24, 2015 at 14:38
  • @Daniel Vérité could ypu please write an answer so I can accept it? Do I have to hstore(NEW.*) to convert it in key=>value pairs but how do I insert those records in a new table in the correct order?
    – Egidi
    Commented Mar 25, 2015 at 11:24

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.