Re: three timestamps/table, use of 'now' in table creation statement

On Thu, Jan 16, 2003 at 07:53:46AM -0800, codeWarrior wrote:
> "Bruno Wolff III" <bruno(at)wolff(dot)to> wrote:
> > Dennis Gearon <gearond(at)cvc(dot)net> wrote:
> > > I want to have three timestamps columns in a table:
> >
> > > What I want to happen is that all three columns get set with the 'now'
> > > value to the nearest second, and they ALL have the exact same value.
> >
> > You will get the same value if the three times are all obtained within
> > the same transaction.
>
> In your table schema:
>
> "change_dt" timestamptz NULL default 'NOW()',
>
> Which makes absolute sense on an INSERT -- INSERT tblName (field) VALUES
> (nval, nval, ) will AUTOMAGICALLY set your fields to their default...
>
> When you UPDATE -- Only update the colums you needs AND change_dt
>
> Ya' know.... UPDATE tblName set dad = '', dada2 = '', change_dt = 'NOW()'...
> WHERE dada = dada...

missed the start of this thread --

how about some rules?

create TABLE _plith (
id serial,
created timestamp(0) default current_timestamp,
modified timestamp(0),
accessed timestamp(0),
dat text,
primary key ( id )
);

create VIEW plith as
select
created,
modified,
accessed,
dat
from
_plith
;

create RULE plith_add as
ON INSERT to plith
do instead (
insert into _plith (
created,
modified,
accessed,
dat
) values (
current_timestamp,
current_timestamp,
current_timestamp,
NEW.dat
);
);

create RULE plith_edit as
ON UPDATE to plith
do instead (
update _plith set
-- created <= leave it alone!,
modified = current_timestamp,
-- accessed = current_timestamp, -- depends on your paradigm
dat = NEW.dat
where
id = NEW.id
;
);

and then -- not sure about this one... proceed at your own risk
-- something like this, perhaps?

create RULE plith_look as -- maybe, probably not...
ON SELECT to plith
do instead (
update _plith set
accessed = current_timestamp
where -- hmm! not sure how this would work...
id = OLD.id
;
select
id,
created,
modified,
accessed,
dat
from
_plith
;
);

judging by some of the docs i've been scanning, this ON SELECT
rule might not work. (if the docs are up to date.)

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !