PostgreSQL中Oracle DBMS_ASSERT.sql_object_name()的等价实现方案

需求说明 #

你要做的这个函数，功能和Oracle自带的SYS.DBMS_ASSERT.SQL_OBJECT_NAME()完全一致：

• 传入一个数据库对象名（支持模式.对象名的格式）
• 如果这个名称对应真实存在的SQL对象（表、视图、函数、存储过程等），直接返回输入的原名称
• 如果对象不存在，抛出ORA-44002类似的「invalid object name」错误

Oracle内置函数的示例效果 #

-- 验证存在的对象，返回原名称
SELECT SYS.DBMS_ASSERT.SQL_OBJECT_NAME('DBMS_ASSERT.sql_object_name') FROM DUAL;
-- 输出结果：DBMS_ASSERT.sql_object_name

-- 验证不存在的对象，触发错误
SELECT SYS.DBMS_ASSERT.SQL_OBJECT_NAME('unknown') FROM DUAL;
-- 错误提示：ORA-44002: invalid object name

自定义实现代码 #

下面是用PL/SQL写的实现，核心逻辑是查询数据字典确认对象是否存在：

CREATE OR REPLACE FUNCTION SQL_OBJECT_NAME(p_obj_name IN VARCHAR2) RETURN VARCHAR2 IS
    v_obj_exists NUMBER;
    v_schema     VARCHAR2(128);
    v_object     VARCHAR2(128);
BEGIN
    -- 拆分模式名和对象名：如果输入带点号，就拆分；否则用当前会话的默认模式
    IF INSTR(p_obj_name, '.') > 0 THEN
        v_schema := UPPER(SUBSTR(p_obj_name, 1, INSTR(p_obj_name, '.') - 1));
        v_object := UPPER(SUBSTR(p_obj_name, INSTR(p_obj_name, '.') + 1));
    ELSE
        v_schema := UPPER(SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'));
        v_object := UPPER(p_obj_name);
    END IF;

    -- 查ALL_OBJECTS确认对象存在，涵盖常见的对象类型
    SELECT 1
    INTO v_obj_exists
    FROM ALL_OBJECTS
    WHERE OWNER = v_schema
      AND OBJECT_NAME = v_object
      AND OBJECT_TYPE IN ('TABLE', 'VIEW', 'FUNCTION', 'PROCEDURE', 'PACKAGE', 'SEQUENCE', 'TYPE');

    -- 对象存在就返回原输入
    RETURN p_obj_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 对象不存在时抛错，错误信息和内置函数一致
        RAISE_APPLICATION_ERROR(-44002, 'invalid object name');
    WHEN TOO_MANY_ROWS THEN
        -- 兜底处理（理论上不会触发）
        RAISE_APPLICATION_ERROR(-44002, 'invalid object name');
END;
/

测试用例 #

-- 测试带模式的存在对象
SELECT SQL_OBJECT_NAME('DBMS_ASSERT.sql_object_name') FROM DUAL;
-- 输出：DBMS_ASSERT.sql_object_name

-- 测试当前模式下的自建表
CREATE TABLE test_table (id NUMBER);
SELECT SQL_OBJECT_NAME('test_table') FROM DUAL;
-- 输出：test_table

-- 测试不存在的对象
SELECT SQL_OBJECT_NAME('non_existent_obj') FROM DUAL;
-- 错误提示：ORA-20001: invalid object name（注：如果要完全用ORA-44002，需要DBA权限添加自定义错误映射，否则默认用用户错误码范围）

几点注意 #

• 要是非得用和内置函数一模一样的ORA-44002错误码，得有ALTER SYSTEM权限去配置错误映射，不然只能用用户自定义的错误码范围（-20000到-20999）
• ALL_OBJECTS的可见范围取决于当前用户权限，如果要验证所有对象（包括没权限访问的），得换成DBA_OBJECTS，同时给函数授权查询这个视图
• 代码里的OBJECT_TYPE列表可以按需扩展，比如加上SYNONYM、INDEX这些类型

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