Re: three timestamps/table, use of 'now' in table creation statement - Mailing list pgsql-general

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