autoupdating mtime column

From: "David Garamond" <davidgaramond(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: autoupdating mtime column
Date: 2006-08-04 12:16:15
Message-ID: 7c33d060608040516n799af906sf74e1c645a47caff@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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;

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2006-08-04 12:49:06 Re: autoupdating mtime column
Previous Message Jonathan Sinclair 2006-08-04 06:55:35