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