pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRULE for mtime recording
2+ messages / 2 participants
[nested] [flat]
* RULE for mtime recording
@ 2005-04-22 01:58 Enrico Weigelt <weigelt@metux.de>
0 siblings, 1 reply; 2+ messages in thread
From: Enrico Weigelt @ 2005-04-22 01:58 UTC (permalink / raw)
To: pgsql-sql
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
--
---------------------------------------------------------------------
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
---------------------------------------------------------------------
-- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: RULE for mtime recording
@ 2005-04-22 08:42 Christoph Haller <ch@rodos.fzk.de>
parent: Enrico Weigelt <weigelt@metux.de>
0 siblings, 0 replies; 2+ messages in thread
From: Christoph Haller @ 2005-04-22 08:42 UTC (permalink / raw)
To: weigelt@metux.de; +Cc: pgsql-sql
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
> ---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-04-22 08:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-22 01:58 RULE for mtime recording Enrico Weigelt <weigelt@metux.de>
2005-04-22 08:42 ` Christoph Haller <ch@rodos.fzk.de>
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