X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id B0C749FB1DD for ; Fri, 4 Aug 2006 09:16:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 05332-08 for ; Fri, 4 Aug 2006 12:16:18 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.190]) by postgresql.org (Postfix) with ESMTP id D3A619FA506 for ; Fri, 4 Aug 2006 09:16:17 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id n28so43549nfc for ; Fri, 04 Aug 2006 05:16:16 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=rKp1SB9VgKH/hybxtj8NmKaeThaPUSkAlewrMD5cWUuUQxpTXEkTkgeEmKolVETbABRPmmbweXd7ESo3vgbvxzzqdV0lKKwxrX6EEXkmqm9R7300/hUfIBlfuDe2KhCdkEV+cogpIfgKqYj+eUxAUkafPsTenFTAEJiQhZrSPZ8= Received: by 10.78.122.11 with SMTP id u11mr799003huc; Fri, 04 Aug 2006 05:16:15 -0700 (PDT) Received: by 10.78.56.16 with HTTP; Fri, 4 Aug 2006 05:16:15 -0700 (PDT) Message-ID: <7c33d060608040516n799af906sf74e1c645a47caff@mail.gmail.com> Date: Fri, 4 Aug 2006 19:16:15 +0700 From: "David Garamond" To: pgsql-sql@postgresql.org Subject: autoupdating mtime column MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_27957_892199.1154693775819" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.551 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_10_20, HTML_MESSAGE, SPF_PASS X-Spam-Level: * X-Archive-Number: 200608/33 X-Sequence-Number: 25991 ------=_Part_27957_892199.1154693775819 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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; ------=_Part_27957_892199.1154693775819 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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;

------=_Part_27957_892199.1154693775819--