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 C07CA9FB2C2 for ; Fri, 4 Aug 2006 13:05:56 -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 75177-04 for ; Fri, 4 Aug 2006 16:05:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id BB3719FB2BE for ; Fri, 4 Aug 2006 13:05:51 -0300 (ADT) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id B3CA074AE9; Fri, 4 Aug 2006 17:05:50 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 9237E4DB39; Fri, 4 Aug 2006 17:05:50 +0100 (BST) Message-ID: <44D3705E.9050304@archonet.com> Date: Fri, 04 Aug 2006 17:05:50 +0100 From: Richard Huxton User-Agent: Thunderbird 1.5.0.5 (X11/20060719) MIME-Version: 1.0 To: David Garamond Cc: pgsql-sql Subject: Re: autoupdating mtime column References: <7c33d060608040516n799af906sf74e1c645a47caff@mail.gmail.com> <44D34242.6000901@archonet.com> <7c33d060608040810j7f722af3g6a3185bf1cb990d6@mail.gmail.com> In-Reply-To: <7c33d060608040810j7f722af3g6a3185bf1cb990d6@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200608/38 X-Sequence-Number: 25996 David Garamond wrote: > On 8/4/06, Richard Huxton 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(). You can't. The trigger knows nothing about the original statement, just the old and new tuple values. You could use SET foo=DEFAULT, but that's neither more or less convenient than now() in my eyes. -- Richard Huxton Archonet Ltd