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@wolff.to> wrote:
> > Dennis Gearon <gearond@cvc.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@serensoft.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/ !