相当于 PostgreSQL 中 Oracle 的 DBMS_ASSERT.sql_object_name() ？

Question

我正试图想出一个函数来验证对象标识符名称。像在Oracle中一样，如果给定的标识符与任何sql对象(表，函数，视图，…)相关联。它返回名称，否则出错。以下是几个例子。

SELECT SYS.DBMS_ASSERT.SQL_OBJECT_NAME('DBMS_ASSERT.sql_object_name') FROM DUAL;
SYS.DBMS_ASSERT.SQL_OBJECT_NAME('DBMS_ASSERT.SQL_OBJECT_NAME')
DBMS_ASSERT.sql_object_name
SELECT SYS.DBMS_ASSERT.SQL_OBJECT_NAME('unknown') FROM DUAL;
ORA-44002: invalid object name

Answer 1

对于表，视图，序列，您通常会转换为regclass:

select 'some_table_I_will_create_later'::regclass; 
ERROR:  relation "some_table_I_will_create_later" does not exist`. 
LINE 1: select 'some_table_I_will_create_later'::regclass;
^

对于过程和函数，它将被强制转换为regproc，因此要获得与DBMS_ASSERT.sql_object_name()等效的函数，您必须遍历可强制转换为的参数的完整列表:

create or replace function assert_sql_object_name(arg text) 
returns text language sql as $function_body$
select coalesce(
to_regclass(arg)::text,
to_regcollation(arg)::text,
to_regoper(arg)::text,
to_regproc(arg)::text,
to_regtype(arg)::text,
to_regrole(quote_ident(arg))::text,
to_regnamespace(quote_ident(arg))::text )
$function_body$;

这些函数的工作方式与普通强制转换相同，除了它们返回null而不是抛出异常。coalesce()在PostgreSQL中的工作原理与在Oracle中的相同，返回它获得的第一个非空参数。

注意unknown在PostgreSQL中是一个伪类型，所以它不能做一个很好的测试。

select assert_sql_object_name('unknown');
--  assert_sql_object_name
-- ------------------------
--  unknown
select assert_sql_object_name('some_table_I_will_create_later');
--  assert_sql_object_name
-- ------------------------
--  null
create table some_table_I_will_create_later(id int);
select assert_sql_object_name('some_table_I_will_create_later');
--      assert_sql_object_name
-- --------------------------------
--  some_table_i_will_create_later
select assert_sql_object_name('different_schema.some_table_I_will_create_later');
--  assert_sql_object_name
-- ------------------------
--  null
create schema different_schema;
alter table some_table_i_will_create_later set schema different_schema;
select assert_sql_object_name('different_schema.some_table_I_will_create_later');
--              assert_sql_object_name
-- -------------------------------------------------
--  different_schema.some_table_i_will_create_later

在线演示

Answer 2

没有直接的等价，但是如果您知道对象的预期类型，则可以将名称强制转换为对象标识符类型之一

对于表，视图和其他在pg_class中有条目的对象，可以将其强制转换为regclass:

select 'pg_catalog.pg_class'::regclass;
select 'public.some_table'::regclass;

如果对象不存在，强制转换将导致错误。

对于函数或过程，您需要将名称强制转换为regproc:

select 'my_schema.some_function'::regproc;

然而，如果这是一个重载函数(即在pg_catalog.pg_proc中存在多个条目)，那么它将导致一个错误多个名为"some_function">的函数。在这种情况下，您需要提供您想要使用类型regprocedureregprocedure代替测试的完整签名，例如:

select 'my_schema.some_function(int4)'::regprocedure;

您可以在PL/pgSQL中创建一个包装器函数，它尝试不同的强制转换来模仿Oracle函数的行为。

---

orafce扩展提供了dbms_assert.object_name的实现