Thread: RULE for mtime recording

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
timestampnot 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
-- 
---------------------------------------------------------------------Enrico Weigelt    ==   metux IT service
 phone:     +49 36207 519931         www:       http://www.metux.de/ fax:       +49 36207 519932         email:
contact@metux.decellphone: +49 174 7066481
 
----------------------------------------------------------------------- DSL ab 0 Euro. -- statische IP -- UUCP --
Hosting-- Webshops --
 
---------------------------------------------------------------------

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
> ---------------------------------------------------------------------