Re: example of insert timestamp

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();