Converting Objectoid and Object Names in PostgreSQL

Converting Objectoid to Object Name in PostgreSQL

Using pg's internal datatype to convert objectoid to object name can simplify the association query for some system views.

Database type conversion of the corresponding type of oid

The following database types can be used to convert the corresponding type of oid (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)

a correspondence

object name	typology	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 not identified for use at this time and are to be researched:
	regprocedure
	regoper
	regconfig

Creating 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 what tables are available in test mode, and the owners of the tables

Traditional table associations use the following SQL to associate pg_class, pg_namespace, pg_roles/pg_user

psql -U test -d testdb
-- query user associated pg_user query
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 associated pg_roles query
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 above, in order to achieve the effect of the query needs to be associated with three tables, the query is more cumbersome, if you use the object conversion is very simple, 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)

Converting object names to oid types

conversion relation

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/role name'::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)

Users/Roles

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)

typology

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)