Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 597F32E0072 for ; Mon, 25 Feb 2008 22:41:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 90257-08 for ; Mon, 25 Feb 2008 22:41:36 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.230]) by postgresql.org (Postfix) with ESMTP id 797AC2E0058 for ; Mon, 25 Feb 2008 22:41:44 -0400 (AST) Received: by qb-out-0506.google.com with SMTP id o12so2872940qba.17 for ; Mon, 25 Feb 2008 18:41:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; bh=WHi1TP1k5pdie+plkvMI0eIJtyiFMvYvciK8YQ4d/I8=; b=OlHBz+Q8IacZHT1Z/CF2WeAQ1hsGc0Xe79UBwugEFoFO/JG0a+rDDCmMU4VkIEj4Rxw0LswaS6TX/8vnCWf9d+/fNJe0Ah+Au6GxmqAxKFqoIdVWY5D1XlLMTeZOKawOdjaSK510gcjQXoYl6MGg5XmxmmGZlFMThvjGZ6KQ860= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=WEB/DhZ5VK5KZE9aM34KP61ZFp0gFdDH4Fu1Jj9i4yRD0WGb4DHJ+duoec6oLrAowO+SU6Wd1glhk4LBVPGwbPry4jbmxm9RMUYjETIdqxjHtYsXcZ04blDnYqOBFEq6YmlITc1UcHaaHCzwovKiZeR+TRBnDK0wjMCDkEJlyjs= Received: by 10.110.92.8 with SMTP id p8mr3119413tib.24.1203993702297; Mon, 25 Feb 2008 18:41:42 -0800 (PST) Received: by 10.70.89.12 with HTTP; Mon, 25 Feb 2008 18:41:42 -0800 (PST) Message-ID: Date: Mon, 25 Feb 2008 20:41:42 -0600 From: "Aaron Bono" To: "Jamie A Lawrence" Subject: Re: example of insert timestamp Cc: "Gregory Williamson" , "David Bear" , pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3695_11891604.1203993702158" References: <200802251821.19453.david.bear@asu.edu> <8B319E5A30FF4A48BE7EEAAF609DB233015E33E0@COMAIL01.digitalglobe.com> X-Google-Sender-Auth: 825a48eff9e90975 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200802/332 X-Sequence-Number: 28406 ------=_Part_3695_11891604.1203993702158 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Mon, Feb 25, 2008 at 8:05 PM, Jamie A Lawrence 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.com http://codeelixir.com ================================================================== ------=_Part_3695_11891604.1203993702158 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.com
  http://codeelixir.com
================================================================== ------=_Part_3695_11891604.1203993702158--