Thread: 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)