Search Postgresql Archives

Re: Help with trigger

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux