From: David Garamond <davidgaramond@gmail.com>
To: pgsql-sql@postgresql.org
Subject: autoupdating mtime column
Date: Fri, 4 Aug 2006 19:16:15 +0700
Message-ID: <7c33d060608040516n799af906sf74e1c645a47caff@mail.gmail.com> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: davidgaramond@gmail.com
Subject: Re: autoupdating mtime column
In-Reply-To: <7c33d060608040516n799af906sf74e1c645a47caff@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox