Re: Help with trigger

On Monday, December 27, 2010 12:56:22 pm Gary Chambers wrote:
> Michael,
>
> > I'm new to PostgreSQL, but have worked with other databases. I'm trying
> > to write a trigger to default a timestamp column to a fixed interval
> > before another. The test setup is as follows:
> >
> > create table test
> > ( date1 timestamp,
> >
> > date2 timestamp
> >
> > );
> >
> > create or replace function t_listing_startdate() returns trigger as
> > $t_listing_startdate$
> >
> > begin
> >
> > if NEW.date2 is null then
> >
> > NEW.date2 := NEW.date1 - interval '7 day';
> >
> > end if;
> > return NEW;
> >
> > end;
> >
> > $t_listing_startdate$ LANGUAGE plpgsql;
> >
> > CREATE TRIGGER t_listing_startdate before insert or update on test
> >
> > for each row execute procedure t_listing_startdate();
> >
> > Insert into test(date1) values('May 4, 2012');
> > INSERT 0 1
> > test=# select * from test;
> >
> > date1 | date2
> >
> > ---------------------+-------
> > 2012-04-27 00:00:00 |
> > (1 row)
>
> With the exception of abbreviating the table (t) and column names (d1 and
> d2), your example as submitted works for me (8.4.5, MacOSX). What version
> of Pg are you using and on which platform?
>
> -- Gary Chambers

I'm running 8.4.2 on Linux