DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
	h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=bz5tioKxIfPphn8BIS27qlgn55xFizHRbrasQR7mk5X1wjyGfpW0846q/CIHoqkI954ShVyB/NZ+fzcbCcIUtO6Xs+iHJexRvfyBy+ShVybaaQl1tzfu2fFvdifnLdnPDj/mgrEcyMdr3aqS629XFrwwEH1ovcXB/WpKWqNuUrM=;
References: 
 <B75CD08C73BD3543B97E4EF3964B7D701F15773D@CITESMBX1.ad.uillinois.edu>
Message-ID: <1360706442.33888.YahooMailNeo@web162906.mail.bf1.yahoo.com>
Date: Tue, 12 Feb 2013 14:00:42 -0800 (PST)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: update Timestamp updated whenever the table is updated
To: "Campbell, Lance" <lance@illinois.edu>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: 
 <B75CD08C73BD3543B97E4EF3964B7D701F15773D@CITESMBX1.ad.uillinois.edu>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

"Campbell, Lance" <lance@illinois.edu> wrote:

> I would like to have a generic strategy for updating a timestamp
> field on some tables whenever the table is updated.=A0 Is there a
> recommended strategy for doing this other than via the SQL UPDATE
> command?

> Example table:
> CREATE TABLE test_table
> (
>=A0=A0 id integer NOT NULL,
>=A0=A0 field1 character varying NOT NULL,
>=A0=A0 field2 character varying NOT NULL,
>=A0=A0 updated_timestamp timestamp with time zone DEFAULT now(),
>=A0=A0 created_timestamp timestamp with time zone DEFAULT now()
> );

A BEFORE UPDATE trigger is probably what you want.=A0 You could write
a single trigger function which could be attached to all tables
with the updated_timestamp column.=A0 For example:

CREATE FUNCTION set_updated_timestamp()
=A0 RETURNS TRIGGER
=A0 LANGUAGE plpgsql
AS $$
BEGIN
=A0 NEW.updated_timestamp :=3D now();
=A0 RETURN NEW;
END;
$$;

Note that the above function depends on a column name, but not a
table name.=A0 You link it to each table like this:

CREATE TRIGGER test_table_update_timestamp
=A0 BEFORE UPDATE ON test_table
=A0 FOR EACH ROW EXECUTE PROCEDURE set_updated_timestamp();

> I think I read something about RULES.

You generally want to avoid RULES, especially where a trigger works
so well.

--=20
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin