9 May 2010

PostgreSQL 8.3 Table with last insterted and creation date

This is a template for PostgreSQL 8.3 which includes

  • «id» Primary Key as AUTO_INCREMENT
  • «ts_insert» date/time of creation of this record
  • «ts_lastmod» date/time of last modification of this record
/*
Template with auto increment id, last modified and insert date

NOTE: replace
 «$tbl» with your table name and
 «$owner» with your owning user

$Id$
*/

-- Function: update_lastmodified_column()

-- DROP FUNCTION update_lastmodified_column();

CREATE OR REPLACE FUNCTION update_lastmodified_column()
 RETURNS trigger AS
$BODY$
 BEGIN
   NEW.ts_lastmod = NOW();
   RETURN NEW;
 END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE
 COST 100;
ALTER FUNCTION update_lastmodified_column() OWNER TO $owner;


-- Table: $tbl

-- DROP TABLE $tbl;

CREATE TABLE $tbl
(
 id serial NOT NULL,
 ts_insert timestamp with time zone DEFAULT now(),
 ts_lastmod timestamp with time zone DEFAULT now(),
 CONSTRAINT $tbl_id PRIMARY KEY (id)
)
WITH (
 OIDS=FALSE
);
ALTER TABLE $tbl OWNER TO $owner;

-- Trigger: update_lastmodified_modtime on $tbl

-- DROP TRIGGER update_lastmodified_modtime ON $tbl;

CREATE TRIGGER update_lastmodified_modtime
 BEFORE UPDATE
 ON $tbl
 FOR EACH ROW
 EXECUTE PROCEDURE update_lastmodified_column();