Re: Rule to fill in value on column on insert

Bradley Kieser <brad(at)kieser(dot)net> writes:
> How do I create a rule that will set a column to a particular value upon
> insert? It's for use within an audit trail and we want to prevent any
> possibility of some rogue code setting it to an incorrect value (it's a
> time stamp) so we don't want to use default values.

Use a trigger function, not a rule. A "BEFORE INSERT" trigger can do
this trivially, eg:

NEW.insertiontime := now();
return NEW;

You'll probably want a BEFORE UPDATE trigger as well, to prevent later
changes:

NEW.insertiontime := OLD.insertiontime;
return NEW;

or if you want to update the column during updates, you could actually
share the first trigger for both purposes.

regards, tom lane