About three years ago we started doing nightly schema dumps of all our databases and then updating these into CVS. That has proven to be pretty useful when there is need to determine when some change was done in database.
On Dec 29, 2008, at 12:30 PM, George Pavlov wrote:
1. not exactly what you were looking for, but i answer this partially by putting a commented-out CVS expansion tag (e.g. $Id:) in the body of the function so that it gets into the catalog and can be searched:
CREATE OR REPLACE FUNCTION foo () RETURNS void AS $BODY$ -- $Id: foo.sql,v 1.6 2008/12/23 00:06:52 gpavlov Exp $ BEGIN ...
and query it by something like this:
select routine_name, substring(routine_definition from E'%#\042-- #\044Id: % Exp #\044#\042%' for '#') as cvs_id from information_schema.routines ;
2. you can also make some inference about the relative timing of object creation based on the OIDs (query pg_catalog.pg_proc rather than information_schema.routines for proc OIDs).
Hmm... It seems to me that since object creation time, being metadata, would be better served being placed in a COMMENT for the object. That would have the added bonus of being able to search in one place (pg_description) across all objects of all types for a given creation/modification date.
Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
