From: Jamie Lawrence <postgres@jal.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problems with NEW.* in triggers
Date: Tue, 4 Nov 2003 18:14:02 -0500
Message-ID: <20031104231402.GB2879@clueinc.net> (raw)
In-Reply-To: <26587.1067986114@sss.pgh.pa.us>
References: <20031104223319.GA2879@clueinc.net>
<26587.1067986114@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgres@jal.org, tgl@sss.pgh.pa.us
Subject: Re: Problems with NEW.* in triggers
In-Reply-To: <20031104231402.GB2879@clueinc.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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