Message-ID: <4268B8E9.B4085A19@rodos.fzk.de>
Date: Fri, 22 Apr 2005 10:42:17 +0200
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: weigelt@metux.de
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: RULE for mtime recording
References: <20050422015852.GA1298@nibiru.borg.metux.de>
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

Enrico Weigelt wrote:
> 
> Hi folks,
> 
> I'd like to write an update rule, which touches the a mtime field
> (=current_timestamp) on normal update - when the mtime field isnt
> explicitly set. If the update query explictly sets an mtime value,
> this value has to be let through.
> 
> my tables look like:
> 
>     -- base class
>     CREATE TABLE inode
>     (
>         inode_id        oid not null default nextval('inode_id_seq'),
>         mtime   timestamp not null default current_timestamp
>     );
> 
>     -- example class
>     CREATE TABLE foo
>     (
>         bar     text
>     ) INHERITS ( inode );
> 
> now if I do
> 
>     UPDATE foo SET bar = 'xyz' WHERE ...
> 
> the mtime should be set to the current time, but on
> 
>     UPDATE foo SET bar = '123', mtime = '2001-09-11' WHERE ...
> 
> we shall have 9/11 as mtime.
> 
> Is this possible with rules ?
> 
> thx

Just ReadingTFM, I'd say this should do: 

CREATE OR REPLACE RULE foo_update_mtime_is_null 
AS ON UPDATE TO foo 
WHERE mtime IS NULL 
DO INSTEAD 
UPDATE foo SET bar = NEW.bar WHERE ... ; 

CREATE OR REPLACE RULE foo_update_mtime_is_not_null 
AS ON UPDATE TO foo 
WHERE mtime IS NOT NULL 
DO INSTEAD 
UPDATE foo SET bar = NEW.bar, mtime = NEW.mtime WHERE ... ; 

I have very few experience with rules, so if this works, 
please let me know. 
Regards, Christoph 


> --
> ---------------------------------------------------------------------
>  Enrico Weigelt    ==   metux IT service
> 
>   phone:     +49 36207 519931         www:       http://www.metux.de/
>   fax:       +49 36207 519932         email:     contact@metux.de
>   cellphone: +49 174 7066481
> ---------------------------------------------------------------------