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