Re: Last modification date for Triggers, functions, tables ....

Didier Gasser-Morlay wrote:
> On 30 April 2010 20:40, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Didier Gasser-Morlay <didiergm(at)gmail(dot)com> writes:
>>> Is there a way to determine the last modification date of the script of a
>>> function or trigger,
>>> Same question for relations and attributes ?
>>> No, that's not tracked. You could get a rough idea from the xmin of the
>>> relevant catalog rows, but we don't store actual timestamps.
>>> regards, tom lane
>
> Too bad, I'll have to find another way then,

That's what Source Control Management and Content Management Systems
were designed for. Don't run any SQL on a production system until it has
been tested and checked into your SCM.

The other half of that is to add a table to the database which tracks
the version, sub-version and patch level of the database. Updating it is
the last step in any schema change, and one column contains the
timestamp when the update was applied. You can also add pointers back to
the version tracking in your SCM. I use variations of this:

-----8<----------------------------------------------
CREATE TABLE schema_changes(
id serial PRIMARY KEY,
majorrelease varchar(2) NOT NULL,
minorrelease varchar(2) NOT NULL,
pointrelease varchar(4) NOT NULL,
patch varchar(4) NOT NULL,
scriptname varchar(50) NOT NULL,
dateapplied timestamp without time zone NOT NULL
);

-- The first baseline schema script should, as the last step, officially
-- install version 1.0 of the database:

INSERT INTO schema_changes (
majorrelease
,minorrelease
,pointrelease
,patch
,scriptname
,dateapplied
)
VALUES (
'01'
,'00'
,'0000'
,'0000'
,'recipedb.pgsql'
,now()
);
-----8<----------------------------------------------

Bob McConnell
N2SPP