IT评测·应用市场-qidao123.com技术社区

标题: PostgreSQL中将对象oid和对象名相互转换 [打印本页]
作者: 千千梦丶琪    时间: 2024-11-11 07:15
标题: 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企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/) Powered by Discuz! X3.4