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.