Database Reference
In-Depth Information
First, we create a return type for the function, and then the function itself as follows:
CREATE TYPE fileinfo AS (
filename text,
filesize bigint,
ctime abstime,
mtime abstime,
atime abstime
);
CREATE OR REPLACE FUNCTION table_file_info(schemaname text, tablename
text)
RETURNS SETOF fileinfo
AS $$
import datetime, glob, os
db_info = plpy.execute("""
select datname as database_name,
current_setting('data_directory') || '/base/' || db.oid as
data_directory
from pg_database db
where datname = current_database()
""")
#return db_info[0]['data_directory']
table_info_plan = plpy.prepare("""
select nspname as schemaname,
relname as tablename,
relfilenode as filename
from pg_class c
join pg_namespace ns on c.relnamespace=ns.oid
where nspname = $1
and relname = $2;
""", ['text', 'text'])
table_info = plpy.execute(table_info_plan, [schemaname, tablename])
filemask = '%s/%s*' % (db_info[0]['data_directory'], table_info[0]
['filename'])
res = []
for filename in glob.glob(filemask):
fstat = os.stat(filename)
res.append((
filename,
fstat.st_size,
datetime.datetime.fromtimestamp(fstat.st_ctime).isoformat(),
datetime.datetime.fromtimestamp(fstat.st_mtime).isoformat(),
datetime.datetime.fromtimestamp(fstat.st_atime).isoformat()
))
return res
$$ LANGUAGE plpythonu;
