迁移SQL Server视图至PostgreSQL：sys.all_objects等效替代方案

PostgreSQL没有直接对应SQL Server sys.all_objects的单一系统视图，但可以通过组合多个系统目录来获取用户定义及系统对象的信息，涵盖视图、函数、存储过程、扩展函数的ID、创建时间与修改时间。

核心系统目录说明 #

• pg_catalog.pg_class：存储表、视图、索引等对象的基础元数据，oid即为对象唯一ID，relcreationtime记录创建时间，relmtime为对象文件最后修改时间（视图定义变更时更新）。
• pg_catalog.pg_proc：存储函数、存储过程（PostgreSQL 11+支持）的元数据，通过prokind区分对象类型（f=函数，p=存储过程）。
• pg_catalog.pg_namespace：关联对象所属的架构（Schema）信息。
• pg_catalog.pg_extension：关联扩展对象，用于识别扩展函数。

具体查询语句 #

1. 获取所有视图（含系统视图与用户视图） #

SELECT
    c.oid AS object_id,
    n.nspname AS schema_name,
    c.relname AS object_name,
    'VIEW' AS object_type,
    c.relcreationtime AS create_time,
    c.relmtime AS modify_time
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'v';

2. 获取所有函数与存储过程 #

SELECT
    p.oid AS object_id,
    n.nspname AS schema_name,
    p.proname AS object_name,
    CASE p.prokind
        WHEN 'f' THEN 'FUNCTION'
        WHEN 'p' THEN 'PROCEDURE'
        WHEN 'a' THEN 'AGGREGATE FUNCTION'
        WHEN 'w' THEN 'WINDOW FUNCTION'
    END AS object_type,
    pg_get_function_creation_time(p.oid) AS create_time,
    -- 间接获取修改时间：查询对象系统文件的最后修改时间
    (SELECT max(mtime) FROM pg_stat_file(pg_relation_filename(p.oid))) AS modify_time
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid;

3. 获取扩展函数（对应SQL Server的扩展存储过程） #

SELECT
    p.oid AS object_id,
    n.nspname AS schema_name,
    p.proname AS object_name,
    'EXTENSION FUNCTION' AS object_type,
    pg_get_function_creation_time(p.oid) AS create_time,
    (SELECT max(mtime) FROM pg_stat_file(pg_relation_filename(p.oid))) AS modify_time
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
JOIN pg_catalog.pg_extension e ON n.oid = e.extnamespace;

关键说明 #

• 对象ID：PostgreSQL中用oid字段标识唯一对象，等价于SQL Server的object_id。
• 创建时间：视图直接读取pg_class.relcreationtime；函数/存储过程需用pg_get_function_creation_time(oid)函数获取。
• 修改时间：视图的relmtime会在视图定义更新时同步；函数/存储过程无原生修改时间字段，上述查询通过读取系统文件修改时间间接获取，也可结合pg_stat_user_functions的统计数据（需注意统计数据可能因系统配置重置）。

内容的提问来源于stack exchange，提问作者Jayamathi