Brendan Jurd <blakjak@blakjak.sytes.net> writes:
I think the idea of the update default has interesting possbilities.
Perhaps what is needed is two classes of defaults.
1. "implicit default" -- any updates to a tuple either not specifying a
value for the target column at all, or specifying DEFAULT will set that
column to the default. This would be useful for our "touch row" or
"last modified" scenario, as discussed in the previous thread.
2. "explicit default" -- this default can only be actioned if requested
deliberately by the user. e.g. UPDATE foo SET a='x', b='y', c=DEFAULT;
How is #2 different from your "slightly different approach"?
Ah, sorry if this was unclear. #2 would mean that the column
definition has an explicit "update default", which could potentially be
different from the "insert default", if that was desired. The
"slightly different approach" would mean that explicit SET to DEFAULT
instructions would just use the insert default. I was just unsure
whether it would be useful in practice to have separate values for the
explicit update default and the insert default.
A slightly different approach would be to not have explicit update
defaults at all, and instead make statements like UPDATE foo SET
c=DEFAULT actually set c to the "insert default" value.
That exists already (and is SQL-standard), but I'm not convinced that
it does the job conveniently. In the example of a time-of-last-change
column, you do not want the user to have to remember to write
SET modtime = DEFAULT.
Agreed, UPDATE SET x = DEFAULT isn't a good solution for the last
modtime column. But that doesn't mean it wouldn't be useful in other
situations.
In fact, you really don't want ordinary users to
be able to set the column at all. If we had per-column privilege
controls (which the spec says we should, and I think we will eventually)
then disallowing write of the modtime column to ordinary users, along
with an update default expression, would get the job done very nicely.
Sounds good -- column based privileges would have a lot of handy
applications.