README for the PostgreSQL lastchange module
===========================================
Revision: 0.2  Date: 2001-02-15

This file and all others provided as part of this package are distributed under the same terms as PostgreSQL itself. See http://www.postgresql.org/ for details.

You can contact the author (Richard Huxton) at dev@archonet.com

As usual, this code comes with no warranty whatsoever - use it at your own risk. See the licence for details.


Provides
========
A way of creating an auto-timestamp on an existing column with a type of "timestamp". Whenever a row in that table is modified, the timestamp is updated accordingly.


Requirements
============
You need PostgreSQL v7.1
You need to have run "createlang plpgsql <dbname>" to provide plpgsql support


Manifest
========
lastchange-install.sql  - creates module functions
lastchange-remove.sql   - removes module functions
lastchange-README.txt   - this file
lastchange-tutorial.txt - describes how/why the module works


Summary
=======
You can add the relevant triggers using a single function call:
    select lastchg_addto(<table-name>,<timestamp column-name>);
You can remove the triggers with:
    select lastchg_remove(<table-name>,<timestamp column-name>);


Bugs/Issues
===========
You may experience problems if you already have triggers on the specified column - this is intended to be a quick solution for newbies.
At present there is no support for a separate "creation" timestamp which is set on insert. This can be done by setting a DEFAULT value on the column and not modifying it.


Usage
=====
--
-- Start by creating our table
--
richardh=> create table foo (a serial, b text, c timestamp);
NOTICE:  CREATE TABLE will create implicit sequence 'foo_a_seq' for SERIAL column 'foo.a'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'foo_a_key' for table 'foo'
CREATE

--
-- Now import the lastchg module (you might want to use the full path)
--
richardh=> \i lastchange-install.sql
CREATE
CREATE

--
-- Create insert/update trigger
--
richardh=> select lastchg_addto('foo','c');
      lastchg_addto
-------------------------
 Created lastchg trigger
(1 row)

--
-- Insert some data
--
richardh=> insert into foo (b) values ('aaa');
INSERT 217867 1
richardh=> insert into foo (b) values ('bbb');
INSERT 217868 1
richardh=> insert into foo (b) values ('ccc');
INSERT 217869 1

richardh=> select * from foo;
 a |  b  |           c
---+-----+------------------------
 1 | aaa | 2001-02-08 09:33:35+00
 2 | bbb | 2001-02-08 09:33:38+00
 3 | ccc | 2001-02-08 09:33:40+00
(3 rows)

--
-- Update some data
--
richardh=> update foo set b='xxx';
UPDATE 3
richardh=> select * from foo;
 a |  b  |           c
---+-----+------------------------
 1 | xxx | 2001-02-08 09:34:41+00
 2 | xxx | 2001-02-08 09:34:41+00
 3 | xxx | 2001-02-08 09:34:41+00
(3 rows)

--
-- Remove the triggers
--
richardh=> select lastchg_remove('foo','c');
     lastchg_remove
-------------------------
 Removed lastchg trigger
(1 row)

--
-- Timestamp shouldn't update now
--
richardh=> update foo set b='yyy';
UPDATE 3
richardh=> select * from foo;
 a |  b  |           c
---+-----+------------------------
 1 | yyy | 2001-02-08 09:34:41+00
 2 | yyy | 2001-02-08 09:34:41+00
 3 | yyy | 2001-02-08 09:34:41+00
(3 rows)