Re: RULE for mtime recording

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(at)metux(dot)de
> cellphone: +49 174 7066481
> ---------------------------------------------------------------------