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?
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. – Daniel Vérité Mar 24 '15 at 14:38