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;
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 |