From: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net> |
---|---|
To: | Joel Burton <joel(at)joelburton(dot)com> |
Cc: | Postgresql Novice List <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: auto update dates |
Date: | 2002-05-23 15:57:48 |
Message-ID: | 20020523155748.GB14520@campbell-lange.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)campbell-lange(dot)net>
<www.campbell-lange.net>
From | Date | Subject | |
---|---|---|---|
Next Message | Rory Campbell-Lange | 2002-05-23 16:04:25 | Re: auto update dates |
Previous Message | Rory Campbell-Lange | 2002-05-23 15:29:05 | use vi mode in psql |