Re: [GENERAL] Help with trigger

[Gary Chambers]

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

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