Detecting changes to certain fields in 'before update' trigger functions
Hi list,
First steps in trigger functions and PL/pgSQL so please bear with me...
How can one detect changes to certain fields in before update trigger
functions?
IF (NEW.<column-name> != OLD.<column-name>) THEN ...
doesn't work, so obviously my understanding of the values of the
varriables NEW and OLD in before update trigger functions is wrong; I
had thought that OLD holds the record as it was before the update, and
that NEW holds the record as it is since the update (but before the
update has been committed)?
How should one go about detecting changes to certain fields in before
update trigger functions?
Any help/advice much appreciated.
Sebastian
On Monday 01 December 2008 7:18:51 am Sebastian Tennant wrote:
Hi list,
First steps in trigger functions and PL/pgSQL so please bear with me...
How can one detect changes to certain fields in before update trigger
functions?IF (NEW.<column-name> != OLD.<column-name>) THEN ...
doesn't work, so obviously my understanding of the values of the
varriables NEW and OLD in before update trigger functions is wrong; I
had thought that OLD holds the record as it was before the update, and
that NEW holds the record as it is since the update (but before the
update has been committed)?How should one go about detecting changes to certain fields in before
update trigger functions?Any help/advice much appreciated.
Sebastian
It works here. Can you be more specific? Full function code, table schema,etc.
Thanks,
--
Adrian Klaver
aklaver@comcast.net
On Mon, Dec 1, 2008 at 7:18 AM, Sebastian Tennant
<sebyte@smolny.plus.com> wrote:
IF (NEW.<column-name> != OLD.<column-name>) THEN ...
The != operator doesn't work the way you might think when nulls are
thrown into the mix. I asked a similar question a while back and was
kindly pointed to the following syntax:
IF( NEW.* IS DISTINCT FROM OLD.* ) THEN ...
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
Quoth Adrian Klaver <aklaver@comcast.net>:
On Monday 01 December 2008 7:18:51 am Sebastian Tennant wrote:
I had thought that OLD holds the record as it was before the update,
and that NEW holds the record as it is since the update (but before
the update has been committed)?
'42.10 Trigger Procedures' seems to confirm this:
"`NEW'
Data type `RECORD'; variable holding the new database row for
`INSERT'/`UPDATE' operations in row-level triggers. This variable
is `NULL' in statement-level triggers.
`OLD'
Data type `RECORD'; variable holding the old database row for
`UPDATE'/`DELETE' operations in row-level triggers. This variable
is `NULL' in statement-level triggers."
It works here. Can you be more specific? Full function code, table schema,etc.
Of course.
######## timestamper.sql starts here ########
-- \i ./timestamper.sql
DROP TABLE IF EXISTS tt;
CREATE TEMP TABLE tt (username character varying(12),
delisted boolean,
created_at timestamp(0) without time zone,
updated_at timestamp(0) without time zone,
delisted_at timestamp(0) without time zone);
CREATE OR REPLACE FUNCTION timestamper() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN NEW.created_at := current_timestamp(0); END IF;
IF (TG_OP = 'UPDATE') THEN
NEW.updated_at := current_timestamp(0);
IF ((NEW.delisted = true) AND (NEW.delisted != OLD.delisted)) THEN
NEW.delisted_at := current_timestamp(0); END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER timestamper_before_insert BEFORE INSERT ON tt FOR EACH ROW
EXECUTE PROCEDURE timestamper();
CREATE TRIGGER timestamper_before_update BEFORE UPDATE ON tt FOR EACH ROW
EXECUTE PROCEDURE timestamper();
-- DROP FUNCTION timestamper() CASCADE;
-- no need to drop temporary tables
######## timesatmper.sql ends here ########
testdb=> \i ./timestamper.sql
DROP TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
CREATE TRIGGER
testdb=> insert into tt values (foo');
INSERT 0 1
testdb=> select * from tt;
-[ RECORD 1 ]--------------------
username | foo
delisted |
created_at | 2008-12-01 16:17:37
updated_at |
delisted_at |
testdb=> update tt set username=bar';
UPDATE 1
testdb=> select * from tt;
-[ RECORD 1 ]--------------------
username | bar
delisted |
created_at | 2008-12-01 16:17:37
updated_at | 2008-12-01 16:18:27
delisted_at |
testdb=> update tt set delisted=true where username='bar';
UPDATE 1
testdb=> select * from tt;
-[ RECORD 1 ]--------------------
username | bar
delisted | t
created_at | 2008-12-01 16:17:37
updated_at | 2008-12-01 16:19:01
delisted_at |
The triggers for the initial insert and the first update do what I want
them to, but the second update (that marks 'foo' as delisted) fails to
update the delisted_at timestamp.
Sebastian
Quoth "Richard Broersma" <richard.broersma@gmail.com>:
On Mon, Dec 1, 2008 at 7:18 AM, Sebastian Tennant
<sebyte@smolny.plus.com> wrote:IF (NEW.<column-name> != OLD.<column-name>) THEN ...
The != operator doesn't work the way you might think when nulls are
thrown into the mix. I asked a similar question a while back and was
kindly pointed to the following syntax:IF( NEW.* IS DISTINCT FROM OLD.* ) THEN ...
That's it! Thanks very much Richard.
I sometimes think this kind of gotcha is purposely buried, or not
addressed at all, in order to force users to read the manual. I wasn't
planning on spending four hours doing just that, but now I suppose I'm
almost glad I did.
Sebastian
On Mon, Dec 1, 2008 at 8:35 AM, Sebastian Tennant
<sebyte@smolny.plus.com> wrote:
IF (NEW.<column-name> != OLD.<column-name>) THEN ...
IF( NEW.* IS DISTINCT FROM OLD.* ) THEN ...
I sometimes think this kind of gotcha is purposely buried, or not
addressed at all, in order to force users to read the manual.
I wouldn't say it is intentionally buried. I would say that the
PostgreSQL manual focuses primarily is on "What are the PG features".
While the manual may at times document some of the good/best
practices to use by combining various PG features, I wouldn't say
that its intention isn't to be an authoritative source on "How to use
PG features."
On the other hand, there are many ANSI-SQL books that focus on good
practices. For example, the need for the "IS DISTINCT FROM" when
dealing with nulls would be discussed in an SQL book. Once you have
the theory down, you can turn to the PostgreSQL manual to find out how
PostgreSQL implements this functionality.
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
Sebastian Tennant <sebyte@smolny.plus.com> writes:
I sometimes think this kind of gotcha is purposely buried, or not
addressed at all, in order to force users to read the manual.
Where exactly do you think we should document it, if not in the manual?
In any case it's SQL-standard behavior that any book about SQL will
tell you.
regards, tom lane
If my perusal of the sql generated by pg_dump is correct, then it
doesn't appear that it's wrapped in a transaction, and thus might be
able to only complete a partial restore?
Or does
psql myDatabase <mypg_dumpfile
wrap the file stream in a transaction?
If not, is there a reason why it can't be done so (some process that
cannot be run as a transaction inside the file)?
Or should I just add begin and commit statements at the beginning and
end of file?
I want to provide a mechanized daily update of one schema into a
differently named database, and I'd like it to rollback if if fails.
-Owen
Quoting Owen Hartnett <owen@clipboardinc.com>:
If my perusal of the sql generated by pg_dump is correct, then it
doesn't appear that it's wrapped in a transaction, and thus might be
able to only complete a partial restore?Or does
psql myDatabase <mypg_dumpfile
Try to use pg_restore with the following option:
-1, --single-transaction
restore as a single transaction
Or psql with the following option:
-1 ("one") execute command file as a single transaction
Kind regards,
Peter
Owen Hartnett <owen@clipboardinc.com> writes:
If my perusal of the sql generated by pg_dump is correct, then it
doesn't appear that it's wrapped in a transaction, and thus might be
able to only complete a partial restore?
That's correct, and intentional. You can use pg_restore's -1 switch
or add begin/end manually if you don't want it to work that way.
regards, tom lane
Owen Hartnett wrote:
If my perusal of the sql generated by pg_dump is correct, then it
doesn't appear that it's wrapped in a transaction, and thus might be
able to only complete a partial restore?
You're right, it is not. Try pg_restore --single-transaction. (You'll
need pg_dump -Fc though.)
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
At 12:37 PM -0500 12/1/08, Tom Lane wrote:
Owen Hartnett <owen@clipboardinc.com> writes:
If my perusal of the sql generated by pg_dump is correct, then it
doesn't appear that it's wrapped in a transaction, and thus might be
able to only complete a partial restore?That's correct, and intentional. You can use pg_restore's -1 switch
or add begin/end manually if you don't want it to work that way.regards, tom lane
Thanks to everybody for their help. You guys are great.
-Owen
Quoth "Richard Broersma" <richard.broersma@gmail.com>:
On Mon, Dec 1, 2008 at 8:35 AM, Sebastian Tennant
<sebyte@smolny.plus.com> wrote:IF (NEW.<column-name> != OLD.<column-name>) THEN ...
IF( NEW.* IS DISTINCT FROM OLD.* ) THEN ...
I sometimes think this kind of gotcha is purposely buried, or not
addressed at all, in order to force users to read the manual.I wouldn't say it is intentionally buried. I would say that the
PostgreSQL manual focuses primarily is on "What are the PG features".
While the manual may at times document some of the good/best practices
to use by combining various PG features, I wouldn't say that its
intention isn't to be an authoritative source on "How to use PG
features."On the other hand, there are many ANSI-SQL books that focus on good
practices. For example, the need for the "IS DISTINCT FROM" when
dealing with nulls would be discussed in an SQL book. Once you have
the theory down, you can turn to the PostgreSQL manual to find out how
PostgreSQL implements this functionality.
That's sound advice and I take your point about the manual focussing on
Postgre features rather than SQL per se. I have read one or two SQL
books but I'm very much a learn by doing person... and the fact is, I
haven't done much doing, until now.
May I wriggle out a little by saying that I didn't really mean what I
said, or rather, I failed to say what I really meant; that it sometimes
feels as if a gotcha has been buried in order to make you read the
manual.
Sebastian
Quoth Tom Lane <tgl@sss.pgh.pa.us>:
Sebastian Tennant <sebyte@smolny.plus.com> writes:
I sometimes think this kind of gotcha is purposely buried, or not
addressed at all, in order to force users to read the manual.Where exactly do you think we should document it, if not in the
manual?
I clearly didn't express myself very well. Let me set the record
straight by saying that my experience with PostgreSQL over the past
three months or so has been fantastic, thanks in no small part to the
clear and comprehensive accompanying manual.
All I meant was that it sometimes _feels_ as if a vital piece of
information has been buried in the manual in order to make you read it.
(I wasn't making a serious point and I didn't expect it to be taken
literally).
Sebastian
P.S. Emacs users of PostgreSQL might like to know that there's a
texinfo version of the manual (version 8.3.3) available for
download from here:
Sebastian Tennant wrote:
P.S. Emacs users of PostgreSQL might like to know that there's a
texinfo version of the manual (version 8.3.3) available for
download from here:
Hmm, we did have a patch to add a texinfo target to the docs Makefile ...
apparently it was never applied. Maybe that's a good idea?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Quoth Alvaro Herrera <alvherre@commandprompt.com>:
Sebastian Tennant wrote:
P.S. Emacs users of PostgreSQL might like to know that there's a
texinfo version of the manual (version 8.3.3) available for
download from here:Hmm, we did have a patch to add a texinfo target to the docs Makefile ...
apparently it was never applied. Maybe that's a good idea?
It's a very good idea IMHO.
I would love to see a texinfo target in the docs Makefile. Nothing
beats Info for convenience.
Sebastian
Sebastian Tennant wrote:
Quoth Alvaro Herrera <alvherre@commandprompt.com>:
Sebastian Tennant wrote:
P.S. Emacs users of PostgreSQL might like to know that there's a
texinfo version of the manual (version 8.3.3) available for
download from here:Hmm, we did have a patch to add a texinfo target to the docs Makefile ...
apparently it was never applied. Maybe that's a good idea?It's a very good idea IMHO.
Hmm, actually now that I look closer, it is there (make postgres.info
does the trick). The build process throws a worrying number of warnings
though.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Quoth Alvaro Herrera <alvherre@commandprompt.com>:
Sebastian Tennant wrote:
Quoth Alvaro Herrera <alvherre@commandprompt.com>:
Sebastian Tennant wrote:
P.S. Emacs users of PostgreSQL might like to know that there's a
texinfo version of the manual (version 8.3.3) available for
download from here:Hmm, we did have a patch to add a texinfo target to the docs Makefile ...
apparently it was never applied. Maybe that's a good idea?It's a very good idea IMHO.
Hmm, actually now that I look closer, it is there (make postgres.info
does the trick). The build process throws a worrying number of warnings
though.
Warnings are better than errors :-)
I'll download the source and have a go myself.
Many thanks Alvaro.
Sebastian