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

Re: example of insert timestamp

From: Steve Holdoway <steve(dot)holdoway(at)firetrust(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: example of insert timestamp
Date: 2008-02-26 01:26:29
Message-ID: 20080226142629.1bc12aa7.steve.holdoway@firetrust.com (view raw or flat)
Thread:
Lists: pgsql-admin
On Mon, 25 Feb 2008 18:21:19 -0700
David Bear <david(dot)bear(at)asu(dot)edu> wrote:

> I've been reading about triggers on insert and found the page at 
> http://www.postgresql.org/docs/8.1/interactive/triggers.html with some sample 
> code in the comments.
> 
> I'm thinking what I want can't really be this involved.
> 
> I want to have a table with a timestamp field that automatically gets the 
> value of now() on insert. The timestamp will never be updated. I assume I 
> need to create a trigger to do this. If there is an easier way, please 
> advise.
> 
> Otherwise, is there any sample code that would should be how to do this? The 
> sample on the page above looks like overkill.
> 
> -- 
> 
> David Bear
> College of Public Programs/ASU
> 411 N Central, Phoenix, AZ 85004
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
Here's a quick example which sets column dateadded to now on insert... can't get much simpler!


create or replace function surbl.AddUrl()
returns TRIGGER AS
$$
BEGIN
-- Must have an url to add
IF ( NEW.url IS NULL )
THEN
	RETURN NULL;
END IF;
NEW.dateadded := now();
RETURN NEW;
END;
$$ language 'plpgsql';

drop trigger addurl_trig ON surbl.rawdata;
create trigger addurl_trig BEFORE INSERT ON surbl.rawdata
FOR EACH ROW
EXECUTE PROCEDURE surbl.AddUrl();

In response to

pgsql-admin by date

Next:From: David BearDate: 2008-02-26 01:46:37
Subject: Re: example of insert timestamp
Previous:From: Aaron BonoDate: 2008-02-26 01:21:21
Subject: Re: example of insert timestamp

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