Re: Update Default - Mailing list pgsql-general

<br /> Tom Lane wrote:<br /><blockquote cite="mid5288.1075256131@sss.pgh.pa.us" type="cite"><pre wrap="">Brendan Jurd
<aclass="moz-txt-link-rfc2396E" href="mailto:blakjak@blakjak.sytes.net"><blakjak@blakjak.sytes.net></a> writes:
</pre><blockquotetype="cite"><pre wrap="">I think the idea of the update default has interesting possbilities.  
 
Perhaps what is needed is two classes of defaults.    </pre></blockquote><pre wrap=""> </pre><blockquote
type="cite"><prewrap="">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.   </pre></blockquote><pre wrap=""> </pre><blockquote
type="cite"><prewrap="">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;   </pre></blockquote><pre wrap="">
How is #2 different from your "slightly different approach"?
 </pre></blockquote> 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
whetherit would be useful in practice to have separate values for the explicit update default and the insert
default.<br/><blockquote cite="mid5288.1075256131@sss.pgh.pa.us" type="cite"><pre wrap=""></pre><blockquote
type="cite"><prewrap="">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.   </pre></blockquote><pre wrap="">
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.  </pre></blockquote> 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.<br /><br /><blockquote
cite="mid5288.1075256131@sss.pgh.pa.us"type="cite"><pre wrap="">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.
 </pre></blockquote> Sounds good -- column based privileges would have a lot of handy applications.<br /><br />
Cheers<br/><br /> BJ<br /><blockquote cite="mid5288.1075256131@sss.pgh.pa.us" type="cite"><pre wrap="">
regards,tom lane
 

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend </pre></blockquote>