To: "David Garamond" <davidgaramond@gmail.com>
cc: pgsql-sql@postgresql.org
Subject: Re: autoupdating mtime column 
In-reply-to: <7c33d060608040811s6dcaa3abia137188076a76781@mail.gmail.com> 
References: <7c33d060608040516n799af906sf74e1c645a47caff@mail.gmail.com>
	<44D34242.6000901@archonet.com>
	<7c33d060608040811s6dcaa3abia137188076a76781@mail.gmail.com>
Comments: In-reply-to "David Garamond" <davidgaramond@gmail.com>
	message dated "Fri, 04 Aug 2006 22:11:14 +0700"
Date: Fri, 04 Aug 2006 12:10:23 -0400
Message-ID: <29505.1154707823@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"David Garamond" <davidgaramond@gmail.com> writes:
> On 8/4/06, Richard Huxton <dev@archonet.com> wrote:
>> 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,

You don't.  A trigger has no way to know the history of the row it's
looking at --- consider the possibility that it was already modified
by earlier triggers.

If you are really intent on having a way to suppress the mtime update
you could dedicate an additional field to the purpose, eg

UPDATE t SET foo=..., bar=..., keepmtime = true ...

and in the trigger something like

	if new.keepmtime then
		new.keepmtime = false;
	else
		new.mtime = now();

As long as nothing else ever touches keepmtime this would work.
Personally I'm dubious that it's worth the trouble --- do you
have a real use-case for suppressing mtime updates?

			regards, tom lane