How to convert object oid and object name to each other in PostgreSQL

Convert object oid to object name in PostgreSQL

Using pg's internal data type to convert object oid to object name can simplify the association query of some system views.

Database type conversion oid of the corresponding type

You can use the following database type to convert the corresponding type of oid (taking pg12 as an example)

postgres=# select typname from pg_type where typname ~ '^reg';
    typname
---------------
 regclass
 regconfig
 regdictionary
 regnamespace
 regoper
 regoperator
 regproc
 regprocedure
 regrole
 regtype
(10 rows)

Corresponding relationship

Object name	type	Conversion rules
pg_class	regclass	pg_class.oid::regclass
pg_ts_dict	regdictionary	pg_ts_dict.oid::regdictionary
pg_namespace	regnamespace	pg_namespace.oid::regnamespace
pg_operator	regoperator	pg_operator.oid::regoperator
pg_proc	regproc	pg_proc.oid::regproc
pg_roles pg_user	regrole	pg_roles.oid::regrole pg_user.usesysid::regrole
pg_type	regtype	pg_type.oid::regtype
	The following types are currently uncertain for the purpose and are to be studied:
	regprocedure
	regoper
	regconfig

Create test data

psql -U postgres
create user test password 'test';
create database testdb with owner=test;
\c testdb
CREATE SCHEMA AUTHORIZATION test;
psql -U test -d testdb
create table test_t1(id int);
create table test_t2(id int);
create table test_t3(id int);

Based on the above test data, query which tables are in test mode and the owner of the table

The traditional table association method uses the following SQL, which can be associated with pg_class, pg_namespace, pg_roles/pg_user

psql -U test -d testdb
-- Query user associationpg_userQuery
SELECT
   AS SCHEMA,
   AS tablename,
   AS OWNER 
FROM
  pg_class t1
  JOIN pg_user t2 ON  = 
  JOIN pg_namespace t3 ON  =  
WHERE
   = 'r' 
  AND  = 'test';
 schema | tablename | owner
--------+-----------+-------
 test   | test_t1   | test
 test   | test_t2   | test
 test   | test_t3   | test
(3 rows)
-- Query user associationpg_rolesQuery
SELECT
   AS SCHEMA,
   AS tablename,
   AS OWNER 
FROM
  pg_class t1
  JOIN pg_roles t2 ON  = 
  JOIN pg_namespace t3 ON  =  
WHERE
   = 'r' 
  AND  = 'test';
 schema | tablename | owner
--------+-----------+-------
 test   | test_t1   | test
 test   | test_t2   | test
 test   | test_t3   | test
(3 rows)

As mentioned above, in order to achieve query effect, three tables need to be associated. Querying is quite complicated, and it is very simple to use object conversion, as follows:

psql -U test -d testdb
SELECT
  relnamespace :: REGNAMESPACE AS SCHEMA,
  relname AS tablename,
  relowner :: REGROLE AS OWNER 
FROM
  pg_class 
WHERE
  relnamespace :: REGNAMESPACE :: TEXT = 'test' 
  AND relkind = 'r';
 schema | tablename | owner
--------+-----------+-------
 test   | test_t1   | test
 test   | test_t2   | test
 test   | test_t3   | test
(3 rows)

Convert object name to oid type

Convert relationship

Object Type	Conversion rules
table	'Table name'::regclass::oid
function/procedure	'Function name/stored procedure name'::regproc::oid
schema	'Schema name'::regnamespace::oid
user/role	'Username/rolename'::regrole::oid
type	'Type Name'::regtype::oid

Test Example

Table conversion

drop table if exists test_t;
create table test_t(id int);
postgres=# select oid from pg_class where relname = 'test_t';
  oid
-------
 16508
(1 row)
postgres=# select 'test_t'::regclass::oid;
  oid
-------
 16508
(1 row)

Function conversion

CREATE OR REPLACE FUNCTION test_fun(
    arg1 INTEGER,
    arg2 INTEGER,
    arg3 TEXT
)
RETURNS INTEGER
AS $$
BEGIN
    RETURN arg1 + arg2;
END;
$$ LANGUAGE plpgsql;
postgres=# select oid,proname from pg_proc where proname = 'test_fun';
  oid  | proname
-------+----------
 16399 | test_fun
(1 row)
postgres=# select 'test_fun'::regproc::oid;
  oid
-------
 16399
(1 row)

Mode conversion

create schema test_schema;
postgres=# select oid,nspname from pg_namespace where nspname='test_schema';
  oid  |   nspname
-------+-------------
 16511 | test_schema
(1 row)
postgres=# select 'test_schema'::regnamespace::oid;
  oid
-------
 16511
(1 row)

User/Role

create user test_user;
postgres=# select usesysid,usename from pg_user where usename='test_user';
 usesysid |  usename
----------+-----------
    16512 | test_user
(1 row)
postgres=# select 'test_user'::regrole::oid;
  oid
-------
 16512
(1 row)

type

CREATE TYPE type_sex AS ENUM ('male', 'female');
postgres=# select oid,typname from pg_type where typname='type_sex';
  oid  | typname
-------+----------
 16514 | type_sex
(1 row)
postgres=# select 'type_sex'::regtype::oid;
  oid
-------
 16514
(1 row)

This is the end of this article about converting object oids and object names in PostgreSQL. For more related content on converting object oids and object names, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!