Re: Help with trigger

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