Обсуждение: Help with trigger
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?
Thanks much
---Michael
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
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.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
On Mon, Dec 27, 2010 at 9:57 AM, Michael Satterwhite <michael@weblore.com> wrote: > CREATE TRIGGER t_listing_startdate before insert or update on test > for each row execute procedure t_listing_startdate(); Now that you've created a trigger function, you need to attached to your table: http://www.postgresql.org/docs/9.0/interactive/sql-createtrigger.html -- Regards, Richard Broersma Jr.
On Monday, December 27, 2010 02:33:40 pm Richard Broersma wrote: > On Mon, Dec 27, 2010 at 9:57 AM, Michael Satterwhite > > <michael@weblore.com> wrote: > > CREATE TRIGGER t_listing_startdate before insert or update on test > > for each row execute procedure t_listing_startdate(); > > Now that you've created a trigger function, you need to attached to your > table: > > http://www.postgresql.org/docs/9.0/interactive/sql-createtrigger.html I've *GOT* to be missing something in this post. You start by quoting the "Create Trigger" that attaches the trigger to the table. Then you tell me that I've got to do what you showed that I did.
On Mon, Dec 27, 2010 at 1:14 PM, Michael Satterwhite <michael@weblore.com> wrote: > I've *GOT* to be missing something in this post. You start by quoting the > "Create Trigger" that attaches the trigger to the table. Then you tell me that > I've got to do what you showed that I did. Oops, your right, I miss-read your post. Sorry. -- Regards, Richard Broersma Jr.
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()
On Monday, December 27, 2010 12:56:22 pm Gary Chambers wrote:
> 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
I'm running 8.4.2 on Linux
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:
Try this pg_dump of a working example:
CREATE FUNCTION t_listing_startdate() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
if new.d2 is null then
new.d2 := new.d1 - interval '7 day';
end if;
return new;
end;
$$;
CREATE TABLE t (
d1 timestamp without time zone,
d2 timestamp without time zone
);
CREATE TRIGGER t_listing_startdate
BEFORE INSERT OR UPDATE ON t
FOR EACH ROW
EXECUTE PROCEDURE t_listing_startdate();
-- Gary Chambers
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
Michael Satterwhite <michael@weblore.com> writes:
> On Monday, December 27, 2010 12:58:40 pm Guillaume Lelarge wrote:
>> Le 27/12/2010 18:57, Michael Satterwhite a �crit :
>>> 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.
> I'm running 8.4.2.
Well, as somebody already pointed out, the example you posted works
fine. When I try it in 8.4.6, I get
# select * from test;
date1 | date2
---------------------+---------------------
2012-05-04 00:00:00 | 2012-04-27 00:00:00
(1 row)
I find it interesting that your quoted result is
# select * from test;
date1 | date2
---------------------+-------
2012-04-27 00:00:00 |
(1 row)
What it looks like from here is there's a typo in the actually-executing
version of the function, such that date1 not date2 is assigned the
week-old date value. Perhaps "\df+ t_listing_startdate" would get
you started towards sorting it out.
regards, tom lane
On Monday, December 27, 2010 03:36:35 pm you wrote: > Michael Satterwhite <michael@weblore.com> writes: > > On Monday, December 27, 2010 12:58:40 pm Guillaume Lelarge wrote: > >> Le 27/12/2010 18:57, Michael Satterwhite a écrit : > >>> 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. > > > > I'm running 8.4.2. > > Well, as somebody already pointed out, the example you posted works > fine. When I try it in 8.4.6, I get > > # select * from test; > date1 | date2 > ---------------------+--------------------- > 2012-05-04 00:00:00 | 2012-04-27 00:00:00 > (1 row) > > I find it interesting that your quoted result is > > # select * from test; > date1 | date2 > ---------------------+------- > 2012-04-27 00:00:00 | > (1 row) > > What it looks like from here is there's a typo in the actually-executing > version of the function, such that date1 not date2 is assigned the > week-old date value. Perhaps "\df+ t_listing_startdate" would get > you started towards sorting it out. You nailed it. I found what was happening - and it works (as everyone said it should). I apologize for not seeing the obvious! Thanks to everyone!
On Monday, December 27, 2010 03:36:35 pm you wrote: > Michael Satterwhite <michael@weblore.com> writes: > > On Monday, December 27, 2010 12:58:40 pm Guillaume Lelarge wrote: > >> Le 27/12/2010 18:57, Michael Satterwhite a écrit : > >>> 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. > > > > I'm running 8.4.2. > > Well, as somebody already pointed out, the example you posted works > fine. When I try it in 8.4.6, I get > > # select * from test; > date1 | date2 > ---------------------+--------------------- > 2012-05-04 00:00:00 | 2012-04-27 00:00:00 > (1 row) > > I find it interesting that your quoted result is > > # select * from test; > date1 | date2 > ---------------------+------- > 2012-04-27 00:00:00 | > (1 row) > > What it looks like from here is there's a typo in the actually-executing > version of the function, such that date1 not date2 is assigned the > week-old date value. Perhaps "\df+ t_listing_startdate" would get > you started towards sorting it out. You nailed it. I found what was happening - and it works (as everyone said it should). I apologize for not seeing the obvious! Thanks to everyone!