openGauss查询表的ddl和dml变动时间

查询自定义对象ddl时间

select pc.relname,
        case object_type when 'r' then '普通表'
		                 when 'i' then '索引'
		                 when 's' then '序列'
		                 when 'v' then '视图'
		                 when 'p' then '存储过程和函数' end as object_type,
		pr.rolname as creator,
		pn.nspname as schemaname,
		pr2.rolname as owner,
		po.ctime,
		po.mtime
		-- po.valid
	from pg_object po
	left join pg_class pc on po.object_oid=pc.oid
	left join pg_roles pr on po.creator = pr.oid
	left join pg_namespace pn on pn.oid = pc.relnamespace
	left join pg_roles pr2 on pr2.oid = pc.relowner
  where pc.relname !~ '^pg_';

pg_object.ctime：表示对象的创建时间，pg_object.mtime：对象的最后修改时间，修改行为包括ALTER操作和GRANT、REVOKE操作。
参考官方文档：pg_object
示例：

omm@postgres=#create table test_t(id int);
CREATE TABLE
-- 查询结果
+---------+-------------+---------+------------+-------+------------------------------+------------------------------+
| relname | object_type | creator | schemaname | owner |            ctime             |            mtime             |
+---------+-------------+---------+------------+-------+------------------------------+------------------------------+
| test_t  | 普通表      | omm     | public     | omm   | 2025-05-29 23:07:30.68295+08 | 2025-05-29 23:07:30.68295+08 |
+---------+-------------+---------+------------+-------+------------------------------+------------------------------+

查询自定义对象dml时间

select schemaname,relname,last_data_changed from pg_stat_user_tables where schemaname = '模式名' and relname = '表名';

示例：

omm@postgres=#insert into test_t values (1);
INSERT 0 1
-- 查询结果
+------------+---------+-------------------------------+
| schemaname | relname |       last_data_changed       |
+------------+---------+-------------------------------+
| public     | test_t  | 2025-05-29 23:09:30.462089+08 |
+------------+---------+-------------------------------+
(1 row)