[SQL] autoupdating mtime column

[David Garamond]

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,
  t text,
  ctime timestamp with time zone,
  mtime timestamp with time zone
);

create or replace function update_times() returns trigger as $$
begin
  if tg_op='INSERT' then
    if NEW.ctime is null then NEW.ctime = NOW(); end if;
    if NEW.mtime is null then NEW.mtime = NOW(); end if;
  elsif tg_op='UPDATE' then
    if NEW.ctime is null then NEW.ctime = OLD.ctime; end if;
    if NEW.mtime is null then NEW.mtime = NOW(); end if;
  end if;
  return NEW;
end;
$$ language plpgsql;

create trigger update_times before update or insert on t1
for each row execute procedure trig1();

-- #1. mtime & ctime set to NOW()
insert into t1 values (1,'text1',null,null);

-- #2. mtime & ctime set to '2001-01-01'
insert into t1 values (2,'text2','2000-01-01','2000-01-01');

-- #3. mtime and ctime set to '2006-06-06'
update t1 set t='new text1',ctime='2006-6-6',mtime='2006-6-6' where id=1;

-- #4. mtime and ctime unchanged
update t1 set t='new text1' where id=1;

-- #5. mtime automatically updated to NOW()
update t1 set t='new text1',mtime=null where id=1;