如何将Oracle的DBMS_METADATA（含XML数据处理程序）转换至PostgreSQL？

嘿，我来帮你把Oracle中基于DBMS_METADATA的逻辑迁移到PostgreSQL，特别是涉及XML数据处理的存储过程和函数部分。首先得明确：PostgreSQL没有直接对标DBMS_METADATA的内置包，但我们可以通过系统目录视图+内置XML函数+自定义存储过程/函数来实现同等功能。

一、先拆解你的Oracle示例逻辑 #

你的Oracle代码是用来获取指定表的对象权限DDL，核心步骤是：

• 打开OBJECT_GRANT类型的元数据句柄
• 添加DDL转换规则并设置格式化参数
• 过滤指定表和所有者
• 批量获取DDL并循环输出

下面我们对应实现PostgreSQL版本，同时重点讲XML相关的转换。

二、PostgreSQL中实现元数据获取（对应Oracle FETCH_DDL） #

PostgreSQL没有句柄式的元数据获取方式，直接查询系统视图就能拿到所需信息。针对你的示例（获取表的权限DDL），我们可以这样做：

1. 基础版：直接生成DDL #

PostgreSQL内置了pg_get_privileges函数来获取对象的权限字符串，结合系统表可以生成DDL：

DO $$
DECLARE
    v_ddl TEXT;
BEGIN
    -- 遍历指定表的权限，生成GRANT语句
    FOR v_ddl IN
        SELECT format('GRANT %s ON TABLE %I.%I TO %I;',
                     array_to_string(privilege_type, ', '),
                     table_schema,
                     table_name,
                     grantee)
        FROM information_schema.table_privileges
        WHERE table_schema = 'OWNER_NAME'
          AND table_name = 'TABLE_NAME'
        LIMIT 10 -- 对应Oracle的SET_COUNT
    LOOP
        RAISE NOTICE 'Output: %', v_ddl;
    END LOOP;
END $$;

2. XML格式元数据的生成（对应Oracle GET_XML） #

如果需要生成XML格式的元数据（类似Oracle的DBMS_METADATA.GET_XML），可以用PostgreSQL的内置XML函数（xmlforest、xmlagg、xmlelement）自定义实现：

CREATE OR REPLACE FUNCTION get_table_privileges_xml(p_schema TEXT, p_table TEXT)
RETURNS XML AS $$
BEGIN
    RETURN (
        SELECT xmlelement(name "object_grants",
                          xmlagg(
                              xmlelement(name "grant",
                                         xmlforest(table_schema AS "schema",
                                                   table_name AS "table",
                                                   grantee AS "grantee",
                                                   privilege_type AS "privileges")
                                        )
                          )
                 )
        FROM information_schema.table_privileges
        WHERE table_schema = p_schema
          AND table_name = p_table
    );
END $$ LANGUAGE plpgsql;

-- 使用示例
SELECT get_table_privileges_xml('OWNER_NAME', 'TABLE_NAME');

这个函数会返回类似OracleGET_XML的XML结构，包含权限的所有元数据。

三、重点：XML数据提交的存储过程/函数转换（对应Oracle IMPORT_XML） #

Oracle中可以用DBMS_METADATA.IMPORT_XML通过XML数据创建/修改对象，PostgreSQL需要自定义函数解析XML并执行DDL：

示例：解析XML权限数据并执行GRANT语句 #

CREATE OR REPLACE FUNCTION execute_grants_from_xml(p_xml XML)
RETURNS VOID AS $$
DECLARE
    v_grant RECORD;
BEGIN
    -- 解析XML中的每个grant节点
    FOR v_grant IN
        SELECT (xpath('/object_grants/grant/schema/text()', x))[1]::TEXT AS schema_name,
               (xpath('/object_grants/grant/table/text()', x))[1]::TEXT AS table_name,
               (xpath('/object_grants/grant/grantee/text()', x))[1]::TEXT AS grantee,
               (xpath('/object_grants/grant/privileges/text()', x))[1]::TEXT AS privileges
        FROM unnest(xpath('/object_grants/grant', p_xml)) AS x
    LOOP
        -- 生成并执行GRANT语句
        EXECUTE format('GRANT %s ON TABLE %I.%I TO %I;',
                       v_grant.privileges,
                       v_grant.schema_name,
                       v_grant.table_name,
                       v_grant.grantee);
        RAISE NOTICE 'Executed: GRANT % ON TABLE %.% TO %;',
                      v_grant.privileges,
                      v_grant.schema_name,
                      v_grant.table_name,
                      v_grant.grantee;
    END LOOP;
END $$ LANGUAGE plpgsql;

-- 使用示例
SELECT execute_grants_from_xml('<object_grants>
    <grant>
        <schema>OWNER_NAME</schema>
        <table>TABLE_NAME</table>
        <grantee>USER1</grantee>
        <privileges>SELECT, INSERT</privileges>
    </grant>
</object_grants>');

四、关键差异总结 #

• 句柄机制：Oracle用OPEN/FETCH_DDL/CLOSE的句柄式操作，PostgreSQL直接查询系统视图或用函数批量处理
• XML处理：Oracle依赖DBMS_METADATA的XML转换参数，PostgreSQL用原生XML函数拼接/解析，更灵活
• DDL生成：PostgreSQL内置pg_get_*def系列函数（比如pg_get_tabledef、pg_get_functiondef）可以直接获取对象DDL，权限用pg_get_privileges

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