Re: I was spoiled by the MySQL timestamp field

FYI, I usually prefer triggers in cases where you want to modify/check
the row as it is being processed, and rules for modifying other
rows/tables as part of row processing, but both do work.

---------------------------------------------------------------------------

will trillich wrote:
> 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/ !
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073