Re: Default values, inserts, and rules...

Sean Chittenden <sean(at)chittenden(dot)org> writes:
> There are two bits going on in this example:
> 1) To get the current value from a sequence, I have to use
> CURRVAL(seq_name) in the actual rule. Not that big of a deal, but
> kind of a PITA that I can't just use NEW.s.
> 2) When the rule does the insert into the t_log table, NEW.c1 doesn't
> contain the default value of 42. How can I do this? I don't want
> to force the application to specify the default values for
> everything.

This is a known bug in 7.2: default values that should be inserted into
unspecified columns of an INSERT aren't inserted till after the rule
rewriter, thus the rule text doesn't see 'em. This is fixed in CVS for
7.3, but I don't believe we have made a back-patch for 7.2 branch.
I believe this bug accounts for both your gripes.

You have to be quite careful with rules that manipulate a default
nextval() though, because rule expansion is basically a textual
macro-expansion operation, and it's very easy to end up with multiple
copies of the default-value expression --- thus leading to multiple
nextval() calls at runtime, which is assuredly not what you want.

On the whole I'd advise using a trigger, not a rule, for this purpose.
The trigger will see the final state of the row (including computed
defaults) and can reliably insert the correct data into the log table.

regards, tom lane