From: Christoph Haller <ch@rodos.fzk.de>
To: weigelt@metux.de
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: RULE for mtime recording
Date: Fri, 22 Apr 2005 10:42:17 +0200
Message-ID: <4268B8E9.B4085A19@rodos.fzk.de> (raw)
References: <20050422015852.GA1298@nibiru.borg.metux.de>
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
> ---------------------------------------------------------------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, weigelt@metux.de
Subject: Re: RULE for mtime recording
In-Reply-To: <4268B8E9.B4085A19@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox