Question 90297 in PostgreSQL Question posted in
The official documentation can be found here.

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

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
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search