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