Re: I was spoiled by the MySQL timestamp field

On Thu, Jan 23, 2003 at 04:26:31AM -0800, Alan T. Miller wrote:
> As someone who is just getting started with PostygreSQL from
> years working with MySQL, it appears that the timestamp data
> type does not behave in the way it did with MySQL. I got used
> to just defining a column as a timestamp and letting the
> database throw the latest time stamp in there whenever a row
> was updated. Is there anything simular in PosgreSQL? How can I
> accomplish something simular inside the database, or am I stuck
> populating the field in some manner as in the following example
> 
> update blah blah blah timestamp = NOW()

triggers or rules can do that with any timestamp (or timestamp(0)
-- no partial-seconds) field. here's a "rules" approach:

	create table _something (
		id serial,
		dat text,
		freshened timestamp(0),
		primary key ( id )
	);

	create view something as
	select
		id,
		dat,
		freshened
	from
		_something;

	create rule something_add as
	on insert to something
	do instead (
		insert into _something (
			--id,
			dat,
			freshened
		) values (
			--let id take care of itself,
			NEW.dat,
			current_timestamp
		);
	);

	create rule something_edit as
	on update to something
	do instead (
		update _something set
			--id = leave it alone,
			dat  = NEW.dat,
			freshened = current_timestamp
		where
			id   = NEW.id
		;
	);

then you can just

	insert into something (dat) values ('yada yada');
	update something set dat = 'here we go' where id = 23978;

and "freshened" takes care of itself.

-- 
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/ !