Re: Proposal: Store "timestamptz" of database creation on "pg_database"

On 01/03/2013 05:04 AM, Robert Haas wrote:
> O
> Yeah, I don't think this is really a problem.  I would expect the psql
> support for this feature to be not a whole lot more complicated than
> that.  Sure, it might be more than 5 lines of raw code if it requires
> an additional version of some query for which we're currently using
> the same version for both PG93 and PG92, but it's hardly fair to cite
> that as an argument for not doing this.  Such changes are almost
> entirely boilerplate.
Here is a pl/python function which gives you "the real" database 
creation time.

CREATE OR REPLACE FUNCTION database_create_ts(INOUT dbname text, OUT 
ctime timestamp)
RETURNS SETOF RECORD
LANGUAGE plpythonu AS
$$
import os, time
res = plpy.execute("""select datname,
                              current_setting('data_directory') ddir,
                              oid as dboid
                         from pg_database where datname like '%s';""" % 
dbname)
for row in res:
     dbpath = '%(ddir)s/base/%(dboid)s' % row
     stat = os.stat(dbpath)
     yield row['datname'], '%04d-%02d-%02d %02d:%02d:%02d+00' % 
time.gmtime(stat.st_ctime)[:6]
$$;

SELECT * FROM database_create_ts('template%');

------------------
Hannu