[EMAIL PROTECTED] wrote:
> This is probably the best way to do this sort of thing, however as an addition,
> for the benefit of the writer of the question, I'd like to warn him that it's
> not exactly the same behavior that a default value gives you.
>
> insert into t1 (a,b) values (1, NULL) will give you different behavior
> for a standard default value and a trigger like the above. In the former
> you'll get a NULL inserted, in the latter, you'll get the default value.
> If you need to insert NULLs, you can either insert and then update, or find
> another value to trigger the behavior on (such as setting a default -1 and
> having the if be IF new.b = -1 THEN ).
>
> Also, there's at least one other case (SET DEFAULT action for referential
> integrity) that grabs the default value and uses it. The system isn't going
> to know that this trigger is effectively a default value, so anything that
> relies on pulling the defaults normally is likely to not work the way you
> expect. If you don't mind losing the ability to update to NULL (or another
> value you've chosen), you could actually run the trigger before insert or
> update which should at least work for the SET DEFAULT ri action.
Thanks for the clarification. I see the difference between a trigger and a default
value. In my case, I want to prevent NULLs from being inserted in this column so
that aspect of the difference isn't a problem. However, in addition to letting the
system know what I'm doing, being able to do something like the following would be
easier to write and understand.
CREATE TABLE t1 (
a INTEGER NOT NULL,
b INTEGER NOT NULL DEFAULT a + 10
);
Is something like this possible? That exact syntax produces "ERROR: Cannot use
attribute(s) in DEFAULT clause." which isn't very promising if I correctly
understand this use of the word "attribute". Also, the CREATE TABLE documentation
is a bit confusing on the matter. At one point it says "Default niladic-function
or user-function means that the default is the value of the specified function at
the time of the INSERT", but shortly afterwards it says "In the current release
(v6.5), Postgres evaluates all default expressions at the time the table is
defined."
Rodney
