Re: Help with trigger

On Monday, December 27, 2010 12:58:40 pm Guillaume Lelarge wrote:
> Le 27/12/2010 18:57, Michael Satterwhite a écrit :
> > 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)
> >
> > I'm obviously missing something ... and probably something obvious. Why
> > is date2 still null?
>
> I'm not sure it'll help you. I copy/pasted your SQL script in my 9.0.2
> release. Worked great.
>
> What does \d says about your table? your trigger could be disabled.

I'm running 8.4.2.
Here's the output

test=# \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------------+-----------
date1 | timestamp without time zone |
date2 | timestamp without time zone |
Triggers:
t_listing_startdate BEFORE INSERT OR UPDATE ON test FOR EACH ROW EXECUTE
PROCEDURE t_listing_startdate()