From: Aaron Bono <aaron.bono@aranya.com>
To: Jamie A Lawrence <postgres@jal.org>
Cc: Gregory Williamson <Gregory.Williamson@digitalglobe.com>
Cc: David Bear <david.bear@asu.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: example of insert timestamp
Date: Mon, 25 Feb 2008 20:41:42 -0600
Message-ID: <bf05e51c0802251841i479790c0xf713282a961fd460@mail.gmail.com> (raw)
In-Reply-To: <DCB7ACF9-D27A-4A54-A4ED-7C58031DA52D@jal.org>
References: <200802251821.19453.david.bear@asu.edu>
<8B319E5A30FF4A48BE7EEAAF609DB233015E33E0@COMAIL01.digitalglobe.com>
<DCB7ACF9-D27A-4A54-A4ED-7C58031DA52D@jal.org>
On Mon, Feb 25, 2008 at 8:05 PM, Jamie A Lawrence <postgres@jal.org> wrote:
>
> On Feb 25, 2008, at 8:19 PM, Gregory Williamson wrote:
> >
> > Perhaps, make the column so it has a default value of now, land then
> > insert without specifying the timestamp column so it gets the
> > default value:
> >
> > create table foo (q_i_time timestamp with time zone not null default
> > now(), someval int);
> >
>
> This is what I do. For many tables, I also add a mod_time column with
> an ON UPDATE trigger similar to the one previously posted. Even when
> not needed for the table itself, in development, it can be very handy
> to compare dodgy inserts/updates with commit logs when bug hunting...
>
>
>
I know you asked for something more like a create timestamp, but if you also
want a modify timestamp, that would definitely need a trigger:
CREATE OR REPLACE FUNCTION "public"."modify_date_stamp_fn" () RETURNS SETOF
opaque AS
'
BEGIN
-- if a trigger insert or update operation occurs
IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
-- assigns the current timestamp
-- into the modify date and time column
NEW.modify_dt := now();
-- displays the new row on an insert/update
RETURN NEW;
END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE TRIGGER "mytable_modify_dt_tr" BEFORE UPDATE
ON "public"."mytable" FOR EACH ROW
EXECUTE PROCEDURE "public"."modify_date_stamp_fn"();
This function is very generic and can be used for any and all of your tables
so you don't have to create a function for each table as long as the column
name is the same.
-Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.comhttp://codeelixir.com
==================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: aaron.bono@aranya.com, postgres@jal.org, Gregory.Williamson@digitalglobe.com, david.bear@asu.edu
Subject: Re: example of insert timestamp
In-Reply-To: <bf05e51c0802251841i479790c0xf713282a961fd460@mail.gmail.com>
References: <200802251821.19453.david.bear@asu.edu> <8B319E5A30FF4A48BE7EEAAF609DB233015E33E0@COMAIL01.digitalglobe.com> <DCB7ACF9-D27A-4A54-A4ED-7C58031DA52D@jal.org> <bf05e51c0802251841i479790c0xf713282a961fd460@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox