eric soroos <eric-psql@soroos.net> writes:
> What I currently do from an outside app:
> insert into history_foo (column1, ...) values (bar, ...);
> update current_foo set column1=bar, ... where pkey=a;
> What I'd like to do is insert, then have a trigger/rule assemble an
> update statement for the current_foo table, only updating the columns
> that are non-null and in the current_foo table.
Is it critical (or even desirable?) for your app to control the values
being inserted into the extra columns of the history table? Or are
those columns well-defined values like timestamps?
The way I'd be inclined to do this is to have the app manipulate
current_foo directly, not touching the history table, and then let
a rule or trigger insert into the history table.
A simple rule would be along the lines of
ON INSERT TO current DO
INSERT INTO history SELECT new.*, current_timestamp, current_user;
You might get better performance with a trigger, however.
regards, tom lane