pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems with NEW.* in triggers
6+ messages / 4 participants
[nested] [flat]
* Problems with NEW.* in triggers
@ 2003-11-04 22:33 Jamie Lawrence <postgres@jal.org>
2003-11-04 22:48 ` Re: Problems with NEW.* in triggers Tom Lane <tgl@sss.pgh.pa.us>
2003-11-04 22:52 ` Re: Problems with NEW.* in triggers Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 6+ messages in thread
From: Jamie Lawrence @ 2003-11-04 22:33 UTC (permalink / raw)
To: pgsql-sql
Hi folks -
I'm having a problem with what looks like it should be trivial.
For the function
create or replace function timestamp_fn() returns opaque as '
begin
NEW.moddate := coalesce(NEW.moddate, now());
return NEW;
end
' language 'plpgsql';
on an after insert or update trigger, never sets moddate to now().
I had thought that if moddate isn't included in an insert or update,
that it would be null in the NEW context, so that this would fall
through to the now() call. (I also tried this as below:
create or replace function timestamp_fn() returns opaque as '
begin
if NEW.moddate is not null then
return NEW;
else
NEW.moddate := now();
return NEW;
end if;
end
' language 'plpgsql';
With the same results.)
Any thoughts on what I'm doing wrong??
-j
--
Jamie Lawrence jal@jal.org
Never eat anything bigger than your head.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problems with NEW.* in triggers
2003-11-04 22:33 Problems with NEW.* in triggers Jamie Lawrence <postgres@jal.org>
@ 2003-11-04 22:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-11-04 23:14 ` Re: Problems with NEW.* in triggers Jamie Lawrence <postgres@jal.org>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-11-04 22:48 UTC (permalink / raw)
To: Jamie Lawrence <postgres@jal.org>; +Cc: pgsql-sql
Jamie Lawrence <postgres@jal.org> writes:
> I had thought that if moddate isn't included in an insert or update,
> that it would be null in the NEW context,
No, it would be whatever the value to be assigned to the column would
be, if the trigger were not present. In particular, during an UPDATE
it's going to contain the old value of the field. In an INSERT it would
be whatever the column's default value is.
I am not sure what your intention is here. If you want the trigger to
force the field to current time, it can certainly do that. If you want
the user to control whether the field is updated, why do you need a
trigger at all?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problems with NEW.* in triggers
2003-11-04 22:33 Problems with NEW.* in triggers Jamie Lawrence <postgres@jal.org>
2003-11-04 22:48 ` Re: Problems with NEW.* in triggers Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-11-04 23:14 ` Jamie Lawrence <postgres@jal.org>
2003-11-05 00:10 ` Re: Problems with NEW.* in triggers Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Jamie Lawrence @ 2003-11-04 23:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Tue, 04 Nov 2003, Tom Lane wrote:
> Jamie Lawrence <postgres@jal.org> writes:
> > I had thought that if moddate isn't included in an insert or update,
> > that it would be null in the NEW context,
>
> No, it would be whatever the value to be assigned to the column would
> be, if the trigger were not present. In particular, during an UPDATE
> it's going to contain the old value of the field. In an INSERT it would
> be whatever the column's default value is.
For an insert, the default is null in this case.
test=# create table trash (moddate timestamp, message text);
CREATE TABLE
test=# create or replace function timestamp_test() returns opaque as '
test'# begin
test'# NEW.moddate := coalesce(NEW.moddate, now());
test'# return NEW;
test'# end
test'# ' language 'plpgsql';
CREATE FUNCTION
test=# create trigger critter_timestamp_test after insert or update on critter for each row execute procedure timestamp_fn();
CREATE TRIGGER
test=# insert into trash (message) values ('hi there');
INSERT 560920 1
test=# insert into trash (message) values ('hi there');
INSERT 560921 1
test=# select * from trash;
moddate | message
---------+----------
| hi there
| hi there
(2 rows)
test=#
I don't understand why moddate isn't getting set to now() in the above.
(Point taken on updates... I was thinking about NEW in slightly
the wrong way for an after trigger.)
> I am not sure what your intention is here. If you want the trigger to
> force the field to current time, it can certainly do that. If you want
> the user to control whether the field is updated, why do you need a
> trigger at all?
Excellent question, sigh. I'm trying to bandaid a bad design choice
until the application can be changed.
Now that you have me thinking about it, an update rule is probably a
better idea.
Thanks for the help, I appreciate it -
-j
--
Jamie Lawrence jal@jal.org
"Remember, half-measures can be very effective if all you deal with are
half-wits."
- Chris Klein
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problems with NEW.* in triggers
2003-11-04 22:33 Problems with NEW.* in triggers Jamie Lawrence <postgres@jal.org>
2003-11-04 22:48 ` Re: Problems with NEW.* in triggers Tom Lane <tgl@sss.pgh.pa.us>
2003-11-04 23:14 ` Re: Problems with NEW.* in triggers Jamie Lawrence <postgres@jal.org>
@ 2003-11-05 00:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-11-05 00:16 ` Re: Problems with NEW.* in triggers Jamie Lawrence <jal@jal.org>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-11-05 00:10 UTC (permalink / raw)
To: Jamie Lawrence <postgres@jal.org>; +Cc: pgsql-sql
Jamie Lawrence <postgres@jal.org> writes:
> I don't understand why moddate isn't getting set to now() in the above.
Josh fingered the problem there --- you need a BEFORE trigger if you
want to affect the data that will be stored. I had missed that little
mistake :-(
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problems with NEW.* in triggers
2003-11-04 22:33 Problems with NEW.* in triggers Jamie Lawrence <postgres@jal.org>
2003-11-04 22:48 ` Re: Problems with NEW.* in triggers Tom Lane <tgl@sss.pgh.pa.us>
2003-11-04 23:14 ` Re: Problems with NEW.* in triggers Jamie Lawrence <postgres@jal.org>
2003-11-05 00:10 ` Re: Problems with NEW.* in triggers Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-11-05 00:16 ` Jamie Lawrence <jal@jal.org>
0 siblings, 0 replies; 6+ messages in thread
From: Jamie Lawrence @ 2003-11-05 00:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Tue, 04 Nov 2003, Tom Lane wrote:
> Jamie Lawrence <postgres@jal.org> writes:
> > I don't understand why moddate isn't getting set to now() in the above.
>
> Josh fingered the problem there --- you need a BEFORE trigger if you
> want to affect the data that will be stored. I had missed that little
> mistake :-(
Yep. Many thanks to both of you.
-j
--
Jamie Lawrence jal@jal.org
If built in great numbers, motels will be used for nothing but illegal
purposes.
- J. Edgar Hoover
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problems with NEW.* in triggers
2003-11-04 22:33 Problems with NEW.* in triggers Jamie Lawrence <postgres@jal.org>
@ 2003-11-04 22:52 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2003-11-04 22:52 UTC (permalink / raw)
To: Jamie Lawrence <postgres@jal.org>; pgsql-sql
Jamie,
> Any thoughts on what I'm doing wrong??
Yes. If you want to modify the new data, you need to use a BEFORE trigger.
AFTER triggers can't modify NEW, just read it.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-11-05 00:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-04 22:33 Problems with NEW.* in triggers Jamie Lawrence <postgres@jal.org>
2003-11-04 22:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-11-04 23:14 ` Jamie Lawrence <postgres@jal.org>
2003-11-05 00:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-11-05 00:16 ` Jamie Lawrence <jal@jal.org>
2003-11-04 22:52 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox