|
원본출처 : http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=10
Submitted on: 03-15-2001
Description:
Last-Change Updater - updates a "last-changed" field on update/insert to a particular table.
Code:
--- README ---
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 " 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(,);
You can remove the triggers with:
select lastchg_remove(,);
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)
--- End of README ---
--- Tutorial ---
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(,)
=========================================
The basic structure of the function is:
create function lastchg_addto(text,text) returns text as '
declare
begin
return
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.
--- End of Tutorial ---
--- lastchg-install.sql ---
-- lastchg_addto(TABLE,COLUMN)
-- Creates a "last changed" trigger on the indicated table/column
-- The column should be of type "timestamp" and is set to the current time
-- whenever a row is inserted/updated.
--
create function lastchg_addto(text,text) returns text as '
declare
tbl alias for $1;
col alias for $2;
trigfn text;
trignm text;
exec1 text;
exec2 text;
begin
trignm := tbl || ''_'' || col || ''_lctrig'';
trigfn := tbl || ''_'' || col || ''_lcupdate()'';
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 || '';'';
execute exec1;
execute exec2;
return ''Created lastchg trigger'';
end;
' language 'plpgsql';
-- lastchg_remove(TABLE,COLUMN)
-- Removes a "last changed" trigger on the indicated table/column
--
create function lastchg_remove(text,text) returns text as '
declare
tbl alias for $1;
col alias for $2;
trigfn text;
trignm text;
begin
trignm := tbl || ''_'' || col || ''_lctrig'';
trigfn := tbl || ''_'' || col || ''_lcupdate()'';
execute ''drop trigger '' || trignm || '' on '' || tbl || '';'';
execute ''drop function '' || trigfn || '';'';
return ''Removed lastchg trigger'';
end;
' language 'plpgsql';
--- End of lastchg-install.sql ---
--- lastchg-remove.sql ---
--
-- Drop the lastchg_xxx functions
--
drop function lastchg_addto(text,text);
drop function lastchg_remove(text,text);
--- End of lastchg-remove.sql ---
|
