Thread: auto update dates
Sorry - I seem to be bombarding the list. I AM reading my copy of Bruce
Momjian's book quite closely, I promise!
I'm trying to make a rule to automatically update the time, date and
timestamp 'modified' fields when a row is updated.
This is what I have tried:
create rule ideas_insert_datetime_mod as on update to ideas
do
update ideas
set d_modified = CURRENT_DATE,
t_modified = CURRENT_TIME,
ts_modified = CURRENT_TIMESTAMP
This creates a cycle condition, and the rule does not operate.
Do I instead need to write a function and then trigger it after each
update action? I have 3 tables each with similar columns, with the same
column names, which I wish to update in the same way.
Thanks for any help.
Rory
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
Check out some code I wrote at http://www.greentechnologist.org/political/Voter/dev/5_trig.sql. There are functions to keep a Created attribute invariant and update Modified as well. I just created a table Audit which exists only to provide common attributes for a number of other tables. I get to re-use the same functions for each table and just re-apply them as triggers where they are needed. Joshua b. Jore ; http://www.greentechnologist.org ; 10012 11010 11022 10202 1012 2122 11020 10202 10202 11002 1020 1012 11102 11102 11102 1201 11001 11002 10211 11020 10202 10202 11002 11021 1201 11010 11020 10211 On Thu, 23 May 2002, Rory Campbell-Lange wrote: > Sorry - I seem to be bombarding the list. I AM reading my copy of Bruce > Momjian's book quite closely, I promise! > > I'm trying to make a rule to automatically update the time, date and > timestamp 'modified' fields when a row is updated. > > This is what I have tried: > > create rule ideas_insert_datetime_mod as on update to ideas > do > update ideas > set d_modified = CURRENT_DATE, > t_modified = CURRENT_TIME, > ts_modified = CURRENT_TIMESTAMP > > This creates a cycle condition, and the rule does not operate. > > Do I instead need to write a function and then trigger it after each > update action? I have 3 tables each with similar columns, with the same > column names, which I wish to update in the same way. > > Thanks for any help. > Rory > -- > Rory Campbell-Lange > <rory@campbell-lange.net> > <www.campbell-lange.net> > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
create table foo (id int, ts timestamp default current_timestamp);
create function update_ts () returns opaque as
'begin
new.ts = current_timestamp;
return new;
end' language plpgsql;
create trigger update_foo before update n foo for each row execute procedyre
update_ts();
and then create the same trigger for your other tables. you can re-use the
function.
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Rory
> Campbell-Lange
> Sent: Thursday, May 23, 2002 9:03 AM
> To: Postgresql Novice List
> Subject: [NOVICE] auto update dates
>
>
> Sorry - I seem to be bombarding the list. I AM reading my copy of Bruce
> Momjian's book quite closely, I promise!
>
> I'm trying to make a rule to automatically update the time, date and
> timestamp 'modified' fields when a row is updated.
>
> This is what I have tried:
>
> create rule ideas_insert_datetime_mod as on update to ideas
> do
> update ideas
> set d_modified = CURRENT_DATE,
> t_modified = CURRENT_TIME,
> ts_modified = CURRENT_TIMESTAMP
>
> This creates a cycle condition, and the rule does not operate.
>
> Do I instead need to write a function and then trigger it after each
> update action? I have 3 tables each with similar columns, with the same
> column names, which I wish to update in the same way.
>
> Thanks for any help.
> Rory
> --
> Rory Campbell-Lange
> <rory@campbell-lange.net>
> <www.campbell-lange.net>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
On 23/05/02, Joshua b. Jore (josh@greentechnologist.org) wrote: > Check out some code I wrote at > http://www.greentechnologist.org/political/Voter/dev/5_trig.sql. There > are functions to keep a Created attribute invariant and update > Modified as well. Hi Joshua Thanks for the mail. I'm looking for something like your function and trigger on your page here: -- DROP FUNCTION UsersUpd(); CREATE FUNCTION UsersUpd() RETURNS OPAQUE AS ' BEGIN UPDATE AuthNames SET Name = NEW.Name WHERE Name = OLD.Name; NEW.Created = OLD.Created; NEW.Modified = current_timestamp; NEW.AuditSeq = nextval(''AuditSeq''); RETURN NEW; END; ' LANGUAGE 'plpgsql' WITH (isstrict); -- SELECT DropTrigger('Users','UsersUpd'); CREATE TRIGGER UsersUpd BEFORE UPDATE ON Users FOR EACH ROW EXECUTE PROCEDURE UsersUpd(); However my version of getting current_timestamp creates an error: update ideas set title = 'three' where title ~ 'one'; NOTICE: Error occurred while executing PL/pgSQL function update_date_time NOTICE: line 3 at assignment ERROR: Bad time external representation '16:11:45.820720+01' Any ideas on why this may be? Thanks for your help Rory -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>
> Hi Joel. > > [For other readers: I'm trying to learn how to update the modified date, > time and timestamp fields in my database - I've deleted the old parts of > the mail as it was getting too long.] > > Thanks a stack for your mail. I've just about got it working! I have a > problem with time typing - if I make this function to just update the > date it does not work. > > create function update_date_time() returns opaque as > 'begin > new.d_modified = current_date; > new.t_modified = current_time; > new.ts_modified = current_timestamp; > return new; > end' language plpgsql; > > create trigger ideas_update_date_time > before update > on ideas > for each row execute procedure update_date_time(); > > brandf=> update ideas set title = 'three' where title ~ 'one'; > NOTICE: Error occurred while executing PL/pgSQL function > update_date_time > NOTICE: line 3 at assignment > ERROR: Bad time external representation '16:11:45.820720+01' current_time returns timezone information, but you're trying to store this in a field that is time w/o timezone information. either coerce this data yourself, or make the field hold timezone info. Better question, though: why are you storing the date, the time, and the timestamp? Much easier to simply store the timestamp. You can always get the date & time from that. This will save you space _and_ time. (Plus, your current problem would become irrelevant). - J. Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant
Hi Joel.
[For other readers: I'm trying to learn how to update the modified date,
time and timestamp fields in my database - I've deleted the old parts of
the mail as it was getting too long.]
Thanks a stack for your mail. I've just about got it working! I have a
problem with time typing - if I make this function to just update the
date it does not work.
create function update_date_time() returns opaque as
'begin
new.d_modified = current_date;
new.t_modified = current_time;
new.ts_modified = current_timestamp;
return new;
end' language plpgsql;
create trigger ideas_update_date_time
before update
on ideas
for each row execute procedure update_date_time();
brandf=> update ideas set title = 'three' where title ~ 'one';
NOTICE: Error occurred while executing PL/pgSQL function
update_date_time
NOTICE: line 3 at assignment
ERROR: Bad time external representation '16:11:45.820720+01'
Before any triggers are added this is what the 'ideas' table looks like:
Table "ideas"
Column | Type | Modifiers
-------------+--------------------------+----------------------------------------------------
id | integer | not null default nextval('"ideas_id_seq"'::text)
hidden | integer | default 0
d_created | date | default date('now'::text)
t_created | time without time zone | default ('now'::text)::time(6) with time zone
ts_created | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
d_modified | date | default date('now'::text)
t_modified | time without time zone | default ('now'::text)::time(6) with time zone
ts_modified | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
creatorid | integer |
title | text |
description | text |
Unique keys: ideas_id_key
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
On 23/05/02, Joel Burton (joel@joelburton.com) wrote:
> current_time returns timezone information, but you're trying to store
> this in a field that is time w/o timezone information. either coerce
> this data yourself, or make the field hold timezone info.
>
> Better question, though: why are you storing the date, the time, and
> the timestamp? Much easier to simply store the timestamp. You can
> always get the date & time from that. This will save you space _and_
> time. (Plus, your current problem would become irrelevant).
Hi Joel
Thanks very much for your mail.
I can see that working with CAST I can extract the date and
(theoretically) the time from the timestamp. Thanks very much for the
suggestion.
brandf=# select cast(current_timestamp as time);
ERROR: Cannot cast type 'timestamp with time zone' to 'time without
time zone'
How do I get around this? I can see various time functions with and
without time zone data, but I don't know how to utilise them.
Thanks
Rory
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
> -----Original Message----- > From: Rory Campbell-Lange [mailto:rory@campbell-lange.net] > Sent: Thursday, May 23, 2002 7:25 PM > To: Joel Burton > Cc: Postgresql Novice List > Subject: Re: [NOVICE] auto update dates > > > On 23/05/02, Joel Burton (joel@joelburton.com) wrote: > > current_time returns timezone information, but you're trying to store > > this in a field that is time w/o timezone information. either coerce > > this data yourself, or make the field hold timezone info. > > > > Better question, though: why are you storing the date, the time, and > > the timestamp? Much easier to simply store the timestamp. You can > > always get the date & time from that. This will save you space _and_ > > time. (Plus, your current problem would become irrelevant). > > Hi Joel > > Thanks very much for your mail. > > I can see that working with CAST I can extract the date and > (theoretically) the time from the timestamp. Thanks very much for the > suggestion. > > brandf=# select cast(current_timestamp as time); > ERROR: Cannot cast type 'timestamp with time zone' to 'time without > time zone' > > How do I get around this? I can see various time functions with and > without time zone data, but I don't know how to utilise them. Rory: SELECT cast(current_timestamp as time with time zone); will do nicely. See the Date/Time functions in the documentation for more help. HTH. Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant
Thanks for the pointers, Joel. On 24/05/02, Joel Burton (joel@joelburton.com) wrote: > > On 23/05/02, Joel Burton (joel@joelburton.com) wrote: > > > current_time returns timezone information, but you're trying to > > > store this in a field that is time w/o timezone information. > > > either coerce this data yourself, or make the field hold timezone > > > info. > > ERROR: Cannot cast type 'timestamp with time zone' > > How do I get around this? I can see various time functions with and > > without time zone data, but I don't know how to utilise them. > SELECT cast(current_timestamp as time with time zone); > will do nicely. See the Date/Time functions in the documentation for > more help. select to_char(timestamp 'now', 'YYYY/MM/DD'); => 2002/05/24 Brilliant! OK, I'm now pointing my local apache to serve up the postgres docs. Sorry for taking up unnecessary bandwidth. Cheers, Rory -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>