Re: auto update dates

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>