[SQL] autoupdating mtime column

Dear all,Please see SQL below. I'm already satisfied with everything except I wish in #4, mtime got automatically updated to NOW() if not explicitly SET in UPDATE statement. Is there a way to make the mtime column behave more like I wanted? Thanks in advance. create table t1 (  id int primary key, 

Re: [SQL] autoupdating mtime column

David Garamond wrote: Dear all, Please see SQL below. I'm already satisfied with everything except I wish in #4, mtime got automatically updated to NOW() if not explicitly SET in UPDATE statement. Is there a way to make the mtime column behave more like I wanted? Thanks in advance. Just che

Re: [SQL] autoupdating mtime column

On 8/4/06, Richard Huxton wrote: David Garamond wrote:> Dear all,>> Please see SQL below. I'm already satisfied with everything except I> wish in> #4, mtime got automatically updated to NOW() if not explicitly SET in > UPDATE> statement. Is there a way to make the mtime column be

Re: [SQL] autoupdating mtime column

On 8/4/06, David Garamond <[EMAIL PROTECTED]> wrote: On 8/4/06, Richard Huxton wrote: > David Garamond wrote: > > Dear all, > > > > Please see SQL below. I'm already satisfied with everything except I > > wish in > > #4, mtime got automatically updated to NOW() if not explicitly SET in > > UPDAT

Re: [SQL] autoupdating mtime column

On 8/4/06, Rodrigo De León <[EMAIL PROTECTED]> wrote: How about:create or replace functionupdate_times()returns trigger as $$beginif TG_OP='INSERT' thennew.ctime = coalesce(new.ctime,now()); new.mtime = coalesce(new.mtime,now()

Re: [SQL] autoupdating mtime column

David Garamond wrote: On 8/4/06, Richard Huxton wrote: David Garamond wrote: > Dear all, > > Please see SQL below. I'm already satisfied with everything except I > wish in > #4, mtime got automatically updated to NOW() if not explicitly SET in > UPDATE > statement. Is there a way to make the mt

Re: [SQL] autoupdating mtime column

"David Garamond" <[EMAIL PROTECTED]> writes: > On 8/4/06, Richard Huxton wrote: >> Just check for OLD.mtime = NEW.mtime, or am I missing something here? > How do I differentiate between: > UPDATE t SET mtime=mtime ...; > in which mtime is specifically set and should not change, You don't. A tri

Re: [SQL] autoupdating mtime column

On 8/4/06, Tom Lane <[EMAIL PROTECTED]> wrote: If you are really intent on having a way to suppress the mtime updateyou could dedicate an additional field to the purpose, egUPDATE t SET foo=..., bar=..., keepmtime = true ...and in the trigger something like if new.keepmtime then

Re: [SQL] autoupdating mtime column

David Garamond wrote: On 8/4/06, Tom Lane <[EMAIL PROTECTED]> wrote: --- do you have a real use-case for suppressing mtime updates? Syncing tables between databases (a la "rsync --times"). Btw, I'm considering temporarily disabling the update_times() trigger when sync-ing. I'd consider runn

Re: [SQL] autoupdating mtime column

On 8/5/06, Richard Huxton wrote: >> have a real use-case for suppressing mtime updates?> Syncing tables between databases (a la "rsync --times"). Btw, I'm> considering temporarily disabling the update_times() trigger when sync-ing. I'd consider running the sync as a different (pr