Thread: automatic timestamp question

I have a table with a structure like:

CREATE TABLE xxx
    (id int,
    modified timestamp default current_timestamp,
    created timestamp default current_timestamp,
    something varchar(10));
    somethingelse varchar(10));

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?

In MySQL the first timestamp field (by default) automatically does this.

In PostgreSQL 7.0.3:
    do I have to explicitly set the modified date?
    do I have to create a stored procedure/trigger of some sort,?
    or can I otherwise define behaviour that automatically happens
    whenever the record is updated?

Thanks.


--
Richard Seymour : Anarchy Software, Inc.
- * - - * - - - * -+- * - - - * - - * -
      `°º¤ø,¸             ¸,ø¤º°'
             `°º¤ø,¸¸,ø¤º°

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/ ----------------------+