A little while ago there was a question about how to automatically update a
timestamp so I drafted the attached code.
NOTE - you need 7.1beta to use the attached code and plpgsql enabled on your
db.
I'm interested in any comments on the attached before I submit it for
possible inclusion in contrib/ (or wherever).
I'm especially interested in any comments on the readme/tutorial from
newbies who haven't experimented with plpgsql before (like me a few weeks
ago). Is it clear enough? Would you prefer HTML?
Also - any special requests for another module? I was thinking of some
functions to provide friendlier access to the system tables (index details,
triggers on a table etc)
PS - developers - where should I submit this stuff to if people like it?
pgsql-doc?
TIA
- Richard Huxton
lastchange-install.sql
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 [EMAIL PROTECTED]
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)
lastchange-remove.sql
Lastchange module - Tutorial
============================
Introduction
============
The lastchange module provides two plpgsql functions to create and remove a trigger on
an indicated table and column. We need to do this in two steps in lastchg_addto():
1. Create a function to update a timestamp column.
2. Create a trigger to call that function.
Removing these is handled by lastchg_remove()
lastchg_addto(<table-name>,<column-name>)
=========================================
The basic structure of the function is:
create function lastchg_addto(text,text) returns text as '
declare
<aliases for parameters here>
<variable definitions here>
begin
<code here>
return <return value>
end;
' language 'plpgsql';
Note that everything from declare..end is quoted and ends in a semicolon. The quoting
will be important later, but first of all we need to declare some variables:
declare
tbl alias for $1;
col alias for $2;
trigfn text;
trignm text;
exec1 text;
exec2 text;
So - we set up aliases for our two parameters so the first can be referred to as "tbl"
and the second as "col" (our table and column names).
We then create two variables to hold the names of our update function (trigfn) and
trigger (trignm).
Now we need to provide some code within our begin...end; block:
begin
trignm := tbl || ''_'' || col || ''_lctrig'';
trigfn := tbl || ''_'' || col || ''_lcupdate()'';
So - if our table is call 'foo' and our column is 'bar' our trigger will be called
'foo_bar_lctrig' and our function 'foo_bar_lcupdate()'. Note that all our literal
strings have to be double-quoted because we are within one set of quotes already.
Then we need to construct some SQL to add this function and trigger to the database:
exec1 := ''create function '' || trigfn || '' returns opaque as '''' '' ||
''begin '' ||
''NEW.'' || col || '' = now(); '' ||
''return NEW; '' ||
''end; '' ||
'' '''' language ''''plpgsql'''';'';
exec2 := ''create trigger '' || trignm ||
'' before insert or update on '' || tbl ||
'' for each row execute procedure '' || trigfn ||
'';'';
These just build two pieces of text that represent the SQL for a CREATE FUNCTION and a
CREATE TRIGGER statement. The only tricky bit is getting the quoting right. Where we
have four quotes in a row, that is because we are putting a quote inside a literal
string which is inside another literal string (yuck).
Finally, we execute this SQL and return a confirmation to the user:
execute exec1;
execute exec2;
return ''Created lastchg trigger'';
That's it - the only difficulty is getting your head around the quoting involved.
lastchg_remove()
================
This basically just creates two DROP statements and executes them (in the reverse
order to which we created them). Note that when you drop a function you need to
specify the parameter types (if any) and when dropping a trigger you need to specify
the table it is attached to.
Tips/Thoughts
=============
You can see the SQL lastchg_addto() creates by removing the "execute" commands and
doing "return exec1" or similar.
When developing I found it useful to put all my code in a little test file so I could
create a dummy table, populate it apply triggers etc. With that and another file that
just drops everything you can debug your code more easily.
There are some limitations on the "execute" command in plpgsql - notably you can't
select into a variable since the execute spins off a subprocess to run the query.
