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 BBDA29FB1E1
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  4 Aug 2006 12:39:18 -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 55162-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  4 Aug 2006 15:39:09 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174])
	by postgresql.org (Postfix) with ESMTP id B2E219FB39F
	for <pgsql-sql@postgresql.org>; Fri,  4 Aug 2006 12:39:08 -0300 (ADT)
Received: by ug-out-1314.google.com with SMTP id m3so85778ugc
	for <pgsql-sql@postgresql.org>; Fri, 04 Aug 2006 08:39:07 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=dyIsBDtEBtE0Fj8g0cHoKrBBX1aV2FLJa+KKpYZHXHb579DxQyVHBh2KjfjBp9IIn4+WOxTWsGyrv38sh/0RuDy2jBX2uWXTWZ4YarYcHDXY44Fj2QoXvhRm+G5eyoyiVSIB5k8Gu81ga8/XMIOtsr2B2kJdnOPTcFjEtL47hQ8=
Received: by 10.66.220.17 with SMTP id s17mr4657358ugg;
	Fri, 04 Aug 2006 08:39:07 -0700 (PDT)
Received: by 10.66.234.16 with HTTP; Fri, 4 Aug 2006 08:39:07 -0700 (PDT)
Message-ID: <a55915760608040839w1a7caad9vd0aeb4e5e5271905@mail.gmail.com>
Date: Fri, 4 Aug 2006 09:39:07 -0600
From: "=?ISO-8859-1?Q?Rodrigo_De_Le=F3n?=" <rdeleonp@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: autoupdating mtime column
In-Reply-To: <7c33d060608040811s6dcaa3abia137188076a76781@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <7c33d060608040516n799af906sf74e1c645a47caff@mail.gmail.com>
	<44D34242.6000901@archonet.com>
	<7c33d060608040811s6dcaa3abia137188076a76781@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.199 tagged_above=0 required=5
	tests=DNS_FROM_RFC_ABUSE, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200608/36
X-Sequence-Number: 25994

On 8/4/06, David Garamond <davidgaramond@gmail.com> wrote:
> On 8/4/06, Richard Huxton <dev@archonet.com> wrote:
> > David Garamond wrote:
> > > 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.
> >
> > Just check for OLD.mtime = NEW.mtime, or am I missing something here?
> >
>
>
> How do I differentiate between:
>
> UPDATE t SET mtime=mtime ...;
>
> in which mtime is specifically set and should not change, and
>
> UPDATE t SET foo=bar ...;
>
> in which mtime is not mentioned and should be updated automagically to
> NOW().

How about:

create or replace function
update_times()
returns trigger as $$
	begin
		if TG_OP='INSERT' then
			new.ctime = coalesce(new.ctime,now());
			new.mtime = coalesce(new.mtime,now());
		elsif TG_OP='UPDATE' then
			new.ctime = old.ctime;
			new.mtime = now();
		end if;
		return new;
	end;
$$ language plpgsql;

Regards,

Rodrigo