Thread: Help with trigger

Help with trigger

From

Michael Satterwhite

Date:

27 December 2010, 14:40:36

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

Re: Help with trigger

From

Gary Chambers

Date:

27 December 2010, 14:56:32

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

Re: Help with trigger

From

Guillaume Lelarge

Date:

27 December 2010, 14:58:54

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

Re: Help with trigger

From

Richard Broersma

Date:

27 December 2010, 16:33:51

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.

Re: Help with trigger

From

Michael Satterwhite

Date:

27 December 2010, 17:20:54

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.

Re: Help with trigger

From

Richard Broersma

Date:

27 December 2010, 17:21:36

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.

Re: Help with trigger

From

Michael Satterwhite

Date:

27 December 2010, 17:23:35

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

Re: Help with trigger

From

Michael Satterwhite

Date:

27 December 2010, 17:24:29

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

Re: Help with trigger

From

Gary Chambers

Date:

27 December 2010, 17:28:50

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

Re: Help with trigger

From

Guillaume Lelarge

Date:

27 December 2010, 17:34:28

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

Re: Help with trigger

From

Tom Lane

Date:

27 December 2010, 17:36:43

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

Re: Help with trigger

From

Michael Satterwhite

Date:

27 December 2010, 18:53:37

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!

Re: Help with trigger

From

Michael Satterwhite

Date:

06 January 2011, 12:44:41

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!