Обсуждение: Date of creation and of change
Hello, could someone enlighten a fairly beginner how to define columns of a table with the following features: CreateDate DEFAULT value should store current date and time ChangeDate DEFAULT value at creation as above and a Trigger function which stores data end time of any change to the data set I hope someone has this quite usual feature handy or at least a pointer where this is described. Thank you very much Andreas.
Andreas Tille wrote: > > Hello, > > could someone enlighten a fairly beginner how to define columns > of a table with the following features: > > CreateDate DEFAULT value should store current date and time create table mytable( CreateDate timestamp default timestamp('now'), ....); > ChangeDate DEFAULT value at creation as above and a Trigger > function which stores data end time of any change > to the data set must be something like that : CREATE FUNCTION myt_stamp () RETURNS OPAQUE AS BEGIN ChangeDate := timestamp(''now''); RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER myt_stamp BEFORE INSERT OR UPDATE ON mytable FOR EACH ROW EXECUTE PROCEDURE myt_stamp(); > I hope someone has this quite usual feature handy or at least a > pointer where this is described. > > Thank you very much > > Andreas.
On Wed, 23 Aug 2000, hlefebvre wrote: > create table mytable( CreateDate timestamp default timestamp('now'), > ....); Thanks, this works. > CREATE FUNCTION myt_stamp () RETURNS OPAQUE AS > BEGIN > ChangeDate := timestamp(''now''); > RETURN NEW; > END; > ' LANGUAGE 'plpgsql'; I tried: web=# CREATE FUNCTION changed_at_timestamp () RETURNS OPAQUE AS ' web'# BEGIN web'# ChangeDate := timestamp(''now''); web'# RETURN NEW; web'# END; web'# ' LANGUAGE 'plpgsql'; CREATE web=# select changed_at_timestamp () ; ERROR: typeidTypeRelid: Invalid type - oid = 0 web=# Is this just the wrong way to test the function? As a beginner I try to validate each new step I do and so I wonder if I insert the Trigger you mentioned > CREATE TRIGGER myt_stamp BEFORE INSERT OR UPDATE ON mytable > FOR EACH ROW EXECUTE PROCEDURE myt_stamp(); the function could cause errors. Sorry, I'm not very familiar with this function stuff :-(. Kind regards Andreas.
On Wed, 23 Aug 2000, hlefebvre wrote: > create table mytable( CreateDate timestamp default timestamp('now'), > ....); I've done a pg_dump <mydb> and there this line was transformed to: "createdat" timestamp DEFAULT '23.08.2000 15:35:16.00 CEST'::"timestamp", I'm afraid if I ever should use this dump as a restore the following dates will be wrong, thought. So is there a save way to use in the dumps? May be the suggestion of Stuart <sgall@iprimus.com.au> On Wed Aug 23 14:36:56 2000 > On insert, however, this will do the job. > > Create table fred (joe integer, createdtime datetime not null default text > 'now'); > > If you dont put the text in you get the date the table was created in all > future inserts. The text force the current now to be used. > ?Is this fixed in 7.0.x???? Would do a better job in this case? What's wrong here? Kind regards Andreas.
Andreas Tille wrote: > I tried: > > web=# CREATE FUNCTION changed_at_timestamp () RETURNS OPAQUE AS ' > web'# BEGIN > web'# ChangeDate := timestamp(''now''); > web'# RETURN NEW; > web'# END; > web'# ' LANGUAGE 'plpgsql'; > CREATE > web=# select changed_at_timestamp () ; > ERROR: typeidTypeRelid: Invalid type - oid = 0 > web=# > > Is this just the wrong way to test the function? Yes. The keywords NEW / OLD are available only in triggers see http://www.postgresql.org/users-lounge/docs/7.0/user/c40874113.htm#AEN4286 > As a beginner I try to validate each new step I do and so I wonder > if I insert the Trigger you mentioned > > > CREATE TRIGGER myt_stamp BEFORE INSERT OR UPDATE ON mytable > > FOR EACH ROW EXECUTE PROCEDURE myt_stamp(); > > the function could cause errors. > > Sorry, I'm not very familiar with this function stuff :-(. > > Kind regards > > Andreas.
Andreas Tille <tillea@rki.de> writes: > On Wed, 23 Aug 2000, hlefebvre wrote: >> create table mytable( CreateDate timestamp default timestamp('now'), >> ....); > I've done a pg_dump <mydb> and there this line was transformed to: > "createdat" timestamp DEFAULT '23.08.2000 15:35:16.00 CEST'::"timestamp", This approach does not work in 7.0 (I think it did work in some prior releases, but not recently). The recommended method is shown in the FAQ: 4.22) How do I create a column that will default to the current time? Use now(): CREATE TABLE test (xint, modtime timestamp default now() ); regards, tom lane
On Wed, 23 Aug 2000, hlefebvre wrote: > Yes. The keywords NEW / OLD are available only in triggers > see > http://www.postgresql.org/users-lounge/docs/7.0/user/c40874113.htm#AEN4286 Well, I believe that, but CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS ' BEGIN ChangedAt := timestamp(''now''); RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TABLE WebSeite (IdWebSeite int4 DEFAULT nextval('seqwebseite'::text) NOT NULL,CreatedAt timestamp DEFAULT now(),changedattimestamp DEFAULT now(), ... ); CREATE TABLE Menu (IdMenu int4 DEFAULT nextval('seqmenu'::text) NOT NULL,CreatedAt timestamp DEFAULT now(),ChangedAt timestampDEFAULT now(), ... ); CREATE TABLE MenuItem (IdMenu int4 DEFAULT nextval('seqmenu'::text) NOT NULL,CreatedAt timestamp DEFAULT now(),ChangedAttimestamp DEFAULT now(), ... ); CREATE TRIGGER webseite_changed_at_timestamp BEFORE INSERT OR UPDATE ON WebSeite FOR EACH ROW EXECUTE PROCEDURE changed_at_timestamp(); CREATE TRIGGER menu_changed_at_timestamp BEFORE INSERT OR UPDATE ON Menu FOR EACH ROW EXECUTE PROCEDURE changed_at_timestamp(); CREATE TRIGGER menuitem_changed_at_timestamp BEFORE INSERT OR UPDATE ON MenuItem FOR EACH ROW EXECUTE PROCEDURE changed_at_timestamp(); web=# insert into menu (IdMenu, ...) values (3, ... ); ERROR: parser: parse error at or near "changedat" What's the problem here. Is there a conflict between the definition with DEFAULT now() and the TRIGGER BEFORE INSERT OR UPDATE. Should perhaps be the DEFAULT in the definition be removed or just the INSERT in the TRIGGER? Or is there a completely different problem? Kind regards Andreas.
Andreas Tille wrote: > > On Wed, 23 Aug 2000, hlefebvre wrote: > > > Yes. The keywords NEW / OLD are available only in triggers > > see > > http://www.postgresql.org/users-lounge/docs/7.0/user/c40874113.htm#AEN4286 > Well, I believe that, but > > CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS ' > BEGIN > ChangedAt := timestamp(''now''); > RETURN NEW; > END; > ' LANGUAGE 'plpgsql'; <snip> > web=# insert into menu (IdMenu, ...) values (3, ... ); > ERROR: parser: parse error at or near "changedat" > > What's the problem here. No I suppose that the problem is the identifier "changedat" is unknown. You must probably prefix it : NEW.changedat CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS ' BEGIN NEW.ChangedAt := timestamp(''now''); RETURNNEW; END;' LANGUAGE 'plpgsql'; I didn't test it, but as pgSQL looks like Oracle, it should be ok :-) regards
On Fri, 25 Aug 2000, hlefebvre wrote: > No I suppose that the problem is the identifier "changedat" is unknown. > > You must probably prefix it : NEW.changedat > > CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS ' > BEGIN > NEW.ChangedAt := timestamp(''now''); > RETURN NEW; > END; > ' LANGUAGE 'plpgsql'; > > I didn't test it, but as pgSQL looks like Oracle, it should be ok :-) This avoids the error message, but doesn't have any effect to the value of ChangedAt. It just remains the same as CreatedAt :-(. Kind regards Andreas.
Andreas Tille wrote: > > On Fri, 25 Aug 2000, hlefebvre wrote: > > > No I suppose that the problem is the identifier "changedat" is unknown. > > > > You must probably prefix it : NEW.changedat > > > > CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS ' > > BEGIN > > NEW.ChangedAt := timestamp(''now''); > > RETURN NEW; > > END; > > ' LANGUAGE 'plpgsql'; > > > > I didn't test it, but as pgSQL looks like Oracle, it should be ok :-) > This avoids the error message, but doesn't have any effect to the value > of ChangedAt. It just remains the same as CreatedAt :-(. > I tried... I fact it seems that it doesn'nt work if you don't use the changedat column in your UPDATE our insert statement. Just put null, and it will be ok I think.
Andreas Tille <tillea@rki.de> writes: >> NEW.ChangedAt := timestamp(''now''); > This avoids the error message, but doesn't have any effect to the value > of ChangedAt. It just remains the same as CreatedAt :-(. I think you are getting burnt by premature constant folding --- see nearby discussion of how to define a column default that gives the time of insertion. You need to write this asNEW.ChangedAt := now(); to prevent the system from reducing timestamp('now') to a constant when the function is first executed. regards, tom lane
On Fri, 25 Aug 2000, Tom Lane wrote: > I think you are getting burnt by premature constant folding --- see > nearby discussion of how to define a column default that gives the > time of insertion. You need to write this as > NEW.ChangedAt := now(); > to prevent the system from reducing timestamp('now') to a constant > when the function is first executed. This doesn't work, too. It just puts allways the constant time when the function was created into the database. May be it has to be escaped in somw way?? I don't know how to follow hlefebvre's hint to "put null" into the field. Bay the way: If we once solved the problem it might be a topic for the FAQ, perhaps? Kind regards Andreas.
Tom Lane wrote: > > Andreas Tille <tillea@rki.de> writes: > >> NEW.ChangedAt := timestamp(''now''); > > > This avoids the error message, but doesn't have any effect to the value > > of ChangedAt. It just remains the same as CreatedAt :-(. > > I think you are getting burnt by premature constant folding --- see > nearby discussion of how to define a column default that gives the > time of insertion. You need to write this as > NEW.ChangedAt := now(); > to prevent the system from reducing timestamp('now') to a constant > when the function is first executed. > > regards, tom lane yep you're right : aegir=# aegir=# drop table menu aegir-# ; DROP aegir=# drop function changed_at_timestamp() ; DROP aegir=# CREATE TABLE Menu ( aegir(# CreatedAt timestamp DEFAULT now(), aegir(# ChangedAt timestamp aegir(# ); CREATE aegir=# aegir=# aegir=# CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS ' aegir'# BEGIN aegir'# NEW.ChangedAt := now(); aegir'# RETURN NEW; aegir'# END; aegir'# ' LANGUAGE 'plpgsql'; CREATE aegir=# aegir=# aegir=# CREATE TRIGGER menu_changed_at_timestamp BEFORE INSERT OR UPDATE ON Men u aegir-# FOR EACH ROW EXECUTE PROCEDURE changed_at_timestamp(); CREATE aegir=# aegir=# insert into menu(createdat) values(null); INSERT 27700 1 aegir=# select * from menu;createdat | changedat -----------+------------------------ | 2000-08-25 16:29:28+02 (1 row) aegir=# insert into menu(createdat) values(null); INSERT 27701 1 aegir=# select * from menu;createdat | changedat -----------+------------------------ | 2000-08-25 16:29:28+02 | 2000-08-25 16:30:53+02 (2 rows) aegir=# update menu set createdat = now(); UPDATE 2 aegir=# select * from menu; createdat | changedat ------------------------+------------------------2000-08-25 16:31:24+02 | 2000-08-25 16:31:24+022000-08-25 16:31:24+02 |2000-08-25 16:31:24+02 (2 rows)
On Fri, 25 Aug 2000, hlefebvre wrote: > Tom Lane wrote: > > NEW.ChangedAt := now(); > > to prevent the system from reducing timestamp('now') to a constant > > when the function is first executed. > > > > regards, tom lane > yep you're right : You both are completely right. Forget about my previous mail. I made a boring mistake and left the old state untouched. Now it works. By the way: Hwo can I prevent Access from warning me about the fact, that "another user" (well it's the postgres server) has changed the data set while I was edditing it? (In general it's no problem, but if I try two changes immediately the second change will be started with this boring warning.) But this is perhaps off topic in this list .... Kind regards and many thanks to you all Andreas.
Andreas Tille wrote: > > On Fri, 25 Aug 2000, Tom Lane wrote: > > > I think you are getting burnt by premature constant folding --- see > > nearby discussion of how to define a column default that gives the > > time of insertion. You need to write this as > > NEW.ChangedAt := now(); > > to prevent the system from reducing timestamp('now') to a constant > > when the function is first executed. > This doesn't work, too. > It just puts allways the constant time when the function was created > into the database. May be it has to be escaped in somw way?? > > I don't know how to follow hlefebvre's hint to "put null" into the > field. As I said, I was wrong, and Tom is right. I was suspecting a problem if the field is not used in the query, but the pb was about the now() function. > Bay the way: If we once solved the problem it might be a topic for > the FAQ, perhaps? IMHO this is a documentation problem. There is no example about such trigger who will modify data, and 'now' is described as "transaction time". also, in date/time function now() is not mentionned (doc about pgSQL 7.0 on the web site at http://www.postgresql.org/users-lounge/docs/7.0/user/functions2872.htm)