postgresql获取表最后更新时间（通过表磁盘存储文件时间）

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

一、创建获取表更新时间的函数

--获取表记录更新时间（通过表磁盘存储文件时间）
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;
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
where filename ~ ('^' || filenode || '([.]?[0-9]+)?$');
END;
$func$;