CREATE OR REPLACE FUNCTION table_file_access_info
(
IN schemaname text, IN tablename text,
OUT last_access timestamp with time zone,
OUT last_change timestamp with time zone
)
LANGUAGE plpgsql AS $func$
DECLARE
tabledir text;
filenode text;
BEGIN
SELECT regexp_replace(
current_setting('data_directory') || '/' || pg_relation_filepath(c.oid),
pg_relation_filenode(c.oid) || '$', ''),
pg_relation_filenode(c.oid)
INTO tabledir, filenode
FROM pg_class c
JOIN pg_namespace ns
ON c.relnamespace = ns.oid
AND c.relname = tablename
AND ns.nspname = schemaname;
RAISE NOTICE 'tabledir: % - filenode: %', tabledir, filenode;
-- find latest access and modification times over all segments
SELECT max((pg_stat_file(tabledir || filename)).access),
max((pg_stat_file(tabledir || filename)).modification)
INTO last_access, last_change
FROM pg_ls_dir(tabledir) AS filename
-- only use files matching <basefilename>[.segmentnumber]
WHERE filename ~ ('^' || filenode || '([.]?[0-9]+)?$');
END;
$func$;
select * from table_file_access_info('public','a');
