Re: Detecting changes to certain fields in 'before update' trigger functions

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