Skip site navigation (1) Skip section navigation (2)

Re: example of insert timestamp

From: "Aaron Bono" <aaron(dot)bono(at)aranya(dot)com>
To: "Jamie A Lawrence" <postgres(at)jal(dot)org>
Cc: "Gregory Williamson" <Gregory(dot)Williamson(at)digitalglobe(dot)com>, "David Bear" <david(dot)bear(at)asu(dot)edu>, pgsql-admin(at)postgresql(dot)org
Subject: Re: example of insert timestamp
Date: 2008-02-26 02:41:42
Message-ID: bf05e51c0802251841i479790c0xf713282a961fd460@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
On Mon, Feb 25, 2008 at 8:05 PM, Jamie A Lawrence <postgres(at)jal(dot)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
==================================================================

In response to

pgsql-admin by date

Next:From: Suresh Gupta VGDate: 2008-02-26 06:08:19
Subject: Latest postgres stable version
Previous:From: Jamie A LawrenceDate: 2008-02-26 02:05:09
Subject: Re: example of insert timestamp

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group