Re: Help with trigger

Le 27/12/2010 22:16, Michael Satterwhite a écrit :
> 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.

I just tried in 8.4.6 and it works with your script.

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

So it isn't disabled (if it were, "Triggers:" would be replaced with
"Disabled triggers:").

Did you try on a new database of the same cluster?

--
Guillaume
http://www.postgresql.fr
http://dalibo.com