Re: three timestamps/table, use of 'now' in table creation statement
От | will trillich |
---|---|
Тема | Re: three timestamps/table, use of 'now' in table creation statement |
Дата | |
Msg-id | 20030123210749.GC3251@mail.serensoft.com обсуждение исходный текст |
Ответ на | Re: three timestamps/table, use of 'now' in table creation statement ("codeWarrior" <GPatnude@adelphia.net>) |
Список | 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/ !
В списке pgsql-general по дате отправления: