Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
Sebastian Tennant wrote: Quoth Alvaro Herrera [EMAIL PROTECTED]: 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: http://www.emacswiki.org/PostGreSQL 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
Quoth Alvaro Herrera [EMAIL PROTECTED]: Sebastian Tennant wrote: Quoth Alvaro Herrera [EMAIL PROTECTED]: 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: http://www.emacswiki.org/PostGreSQL 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
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 [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
On Mon, Dec 1, 2008 at 7:18 AM, Sebastian Tennant [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
Quoth Adrian Klaver [EMAIL PROTECTED]: 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
Quoth Richard Broersma [EMAIL PROTECTED]: On Mon, Dec 1, 2008 at 7:18 AM, Sebastian Tennant [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
On Mon, Dec 1, 2008 at 8:35 AM, Sebastian Tennant [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
Sebastian Tennant [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
Quoth Richard Broersma [EMAIL PROTECTED]: On Mon, Dec 1, 2008 at 8:35 AM, Sebastian Tennant [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
Quoth Tom Lane [EMAIL PROTECTED]: Sebastian Tennant [EMAIL PROTECTED] 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: http://www.emacswiki.org/PostGreSQL -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
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: http://www.emacswiki.org/PostGreSQL 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
Quoth Alvaro Herrera [EMAIL PROTECTED]: 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: http://www.emacswiki.org/PostGreSQL 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
