Help with trigger

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

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.

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!