PostgreSQL中将对象oid和对象名相互转换

PostgreSQL中将对象oid转为对象名

使用pg的内部数据范例将对象oid转为对象名，可以简化一些系统视图的关联查询。
数据库范例转换对应范例的oid

可以用以下数据库范例转换对应范例的oid（以pg12为例）

1. postgres=# select typname from pg_type where typname ~ '^reg';
2.     typname
3. ---------------
4. regclass
5. regconfig
6. regdictionary
7. regnamespace
8. regoper
9. regoperator
10. regproc
11. regprocedure
12. regrole
13. regtype
14. (10 rows)

复制代码

对应关系

对象名称范例转换规则pg_classregclasspg_class.oid::regclasspg_ts_dictregdictionarypg_ts_dict.oid::regdictionarypg_namespaceregnamespacepg_namespace.oid::regnamespacepg_operatorregoperatorpg_operator.oid::regoperatorpg_procregprocpg_proc.oid::regprocpg_roles
pg_userregrolepg_roles.oid::regrole
pg_user.usesysid::regrolepg_typeregtypepg_type.oid::regtype以下几个范例暂不确定用途，待研究：regprocedureregoperregconfig创建测试数据

1. psql -U postgres
2. create user test password 'test';
3. create database testdb with owner=test;
4. \c testdb
5. CREATE SCHEMA AUTHORIZATION test;
6. psql -U test -d testdb
7. create table test_t1(id int);
8. create table test_t2(id int);
9. create table test_t3(id int);

复制代码

基于如上测试数据，查询test模式下有哪些表，以及表的owner
传统表关联的方式使用以下SQL，关联pg_class、pg_namespace、pg_roles/pg_user

1. psql -U test -d testdb
2. -- 查询用户关联pg_user查询
3. SELECT
4.   t3.nspname AS SCHEMA,
5.   t1.relname AS tablename,
6.   t2.usename AS OWNER
7. FROM
8.   pg_class t1
9.   JOIN pg_user t2 ON t1.relowner = t2.usesysid
10.   JOIN pg_namespace t3 ON t1.relnamespace = t3.OID
11. WHERE
12.   t1.relkind = 'r'
13.   AND t3.nspname = 'test';
15. schema | tablename | owner
16. --------+-----------+-------
17. test   | test_t1   | test
18. test   | test_t2   | test
19. test   | test_t3   | test
20. (3 rows)
22. -- 查询用户关联pg_roles查询
23. SELECT
24.   t3.nspname AS SCHEMA,
25.   t1.relname AS tablename,
26.   t2.rolname AS OWNER
27. FROM
28.   pg_class t1
29.   JOIN pg_roles t2 ON t1.relowner = t2.oid
30.   JOIN pg_namespace t3 ON t1.relnamespace = t3.OID
31. WHERE
32.   t1.relkind = 'r'
33.   AND t3.nspname = 'test';
35. schema | tablename | owner
36. --------+-----------+-------
37. test   | test_t1   | test
38. test   | test_t2   | test
39. test   | test_t3   | test
40. (3 rows)

复制代码

如上为了实现查询效果需要关联三张表，查询比较繁琐，如果使用对象转换就很简单了，如下：

1. psql -U test -d testdb
2. SELECT
3.   relnamespace :: REGNAMESPACE AS SCHEMA,
4.   relname AS tablename,
5.   relowner :: REGROLE AS OWNER
6. FROM
7.   pg_class
8. WHERE
9.   relnamespace :: REGNAMESPACE :: TEXT = 'test'
10.   AND relkind = 'r';
12. schema | tablename | owner
13. --------+-----------+-------
14. test   | test_t1   | test
15. test   | test_t2   | test
16. test   | test_t3   | test
17. (3 rows)

复制代码

将对象名转为oid范例

转换关系

对象范例转换规则table'表名'::regclass:idfunction/procedure'函数名/存储过程名'::regproc:idschema'模式名'::regnamespace:iduser/role'用户名/角色名'::regrole:idtype'范例名称'::regtype:id测试示例

表转换

1. drop table if exists test_t;
2. create table test_t(id int);
4. postgres=# select oid from pg_class where relname = 'test_t';
5.   oid
6. -------
7. 16508
8. (1 row)
10. postgres=# select 'test_t'::regclass::oid;
11.   oid
12. -------
13. 16508
14. (1 row)

复制代码

函数转换

1. CREATE OR REPLACE FUNCTION test_fun(
2.     arg1 INTEGER,
3.     arg2 INTEGER,
4.     arg3 TEXT
5. )
6. RETURNS INTEGER
7. AS $$
8. BEGIN
9.     RETURN arg1 + arg2;
10. END;
11. $$ LANGUAGE plpgsql;
14. postgres=# select oid,proname from pg_proc where proname = 'test_fun';
15.   oid  | proname
16. -------+----------
17. 16399 | test_fun
18. (1 row)
20. postgres=# select 'test_fun'::regproc::oid;
21.   oid
22. -------
23. 16399
24. (1 row)

复制代码

模式转换

1. create schema test_schema;
3. postgres=# select oid,nspname from pg_namespace where nspname='test_schema';
4.   oid  |   nspname
5. -------+-------------
6. 16511 | test_schema
7. (1 row)
9. postgres=# select 'test_schema'::regnamespace::oid;
10.   oid
11. -------
12. 16511
13. (1 row)

复制代码

用户/角色

1. create user test_user;
3. postgres=# select usesysid,usename from pg_user where usename='test_user';
4. usesysid |  usename
5. ----------+-----------
6.     16512 | test_user
7. (1 row)
9. postgres=# select 'test_user'::regrole::oid;
10.   oid
11. -------
12. 16512
13. (1 row)

复制代码

范例

1. CREATE TYPE type_sex AS ENUM ('male', 'female');
3. postgres=# select oid,typname from pg_type where typname='type_sex';
4.   oid  | typname
5. -------+----------
6. 16514 | type_sex
7. (1 row)
9. postgres=# select 'type_sex'::regtype::oid;
10.   oid
11. -------
12. 16514
13. (1 row)

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。