On Wed, Jan 10, 2001 at 05:20:26PM -0800, Richard Seymour wrote:
:
: What I want is for the modified field to automatically pop in the
: current time whenever I update the record. The typical update would be
: something like:
:
: UPDATE xxx SET
: something = "hello",
: somethingelse = "goodbye" where id = 2;
:
: What is the easiest, most generic way to do this?
Some lines out of my scripts:
$query1 = sprintf "alter table $table
add modtime timestamp DEFAULT now() NOT NULL;";
$query3 = sprintf "alter table $table
add moduser text default 'initial' NOT NULL;";
$triggername = "${table}_modstamp";
$query2 = sprintf "CREATE TRIGGER $triggername
BEFORE INSERT OR UPDATE ON $table
FOR EACH ROW
EXECUTE PROCEDURE modstamp();";
CREATE FUNCTION modstamp() RETURNS opaque AS '
BEGIN
NEW.modtime := now();
NEW.moduser := getpgusername();
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
Regards,
--
+-----------------------------------------------------------------------+
| Henk van Lingen, Systems Administrator, <henkvl@cs.uu.nl> |
| Dept. of Computer Science, Utrecht University. phone: +31-30-2535278 |
+--------------------- http://henk.vanlingen.net/ ----------------------+
