equivalent of Oracle's DBMS_ASSERT.sql_object_name() in PostgreSQL?-CodePudding

I'm trying to come up with a function to verify the object identifier name. Like in Oracle, if a given identifier associated with any sql object (tables, functions, views,... ) It returns the name as it is else error out. Following are few examples.

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

CodePudding user response：

For tables, views, sequences, you'd typically cast to 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;
               ^

For procedures and functions, it'd be a cast to regproc instead, so to get a function equivalent to DBMS_ASSERT.sql_object_name() you'd have to go through the full list of what the argument could be cast to:

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$;

These functions work the same as a plain cast, except they return null instead of throwing an exception. coalesce() works the same in PostgreSQL as it does in Oracle, returning the first non-null argument it gets.

Note that unknown is a pseudo-type in PostgreSQL, so it doesn't make a good test.

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

Online demo

CodePudding user response：

There is no direct equivalent, but if you know the expected type of the object, you can cast the name to one of the Object Identifier Types

For tables, views and other objects that have an entry in pg_class, you can cast it to to regclass:

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

The cast will result in an error if the object does not exist.

For functions or procedures you need to cast the name to regproc:

select 'my_schema.some_function'::regproc;

However, if that is an overloaded function (i.e. multiple entries exist in pg_catalog.pg_proc, then it would result in an error more than one function named "some_function". In that case you need to provide the full signature you want to test using the type regprocedureregprocedure instead, e.g.:

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

You can create a wrapper function in PL/pgSQL that tries the different casts to mimic the behaviour of the Oracle function.

The orafce extensions provides an implementation of dbms_assert.object_name