我要评论postgresql中将对象oid转为对象名
使用pg的内部数据类型将对象oid转为对象名，可以简化一些系统视图的关联查询。
数据库类型转换对应类型的oid
可以用以下数据库类型转换对应类型的oid（以pg12为例）
postgres=# select typname from pg_type where typname ~ '^reg';
typname
---------------
regclass
regconfig
regdictionary
regnamespace
regoper
regoperator
regproc
regprocedure
regrole
regtype
(10 rows)对应关系
| 对象名称 | 类型 | 转换规则 |
|---|---|---|
| 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 |
| 以下几个类型暂不确定用途，待研究： | ||
| regprocedure | ||
| regoper | ||
| regconfig |
创建测试数据
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);
基于如上测试数据，查询test模式下有哪些表，以及表的owner
传统表关联的方式使用以下sql，关联pg_class、pg_namespace、pg_roles/pg_user
psql -u test -d testdb -- 查询用户关联pg_user查询 select t3.nspname as schema, t1.relname as tablename, t2.usename as owner from pg_class t1 join pg_user t2 on t1.relowner = t2.usesysid join pg_namespace t3 on t1.relnamespace = t3.oid where t1.relkind = 'r' and t3.nspname = 'test'; schema | tablename | owner --------+-----------+------- test | test_t1 | test test | test_t2 | test test | test_t3 | test (3 rows) -- 查询用户关联pg_roles查询 select t3.nspname as schema, t1.relname as tablename, t2.rolname as owner from pg_class t1 join pg_roles t2 on t1.relowner = t2.oid join pg_namespace t3 on t1.relnamespace = t3.oid where t1.relkind = 'r' and t3.nspname = 'test'; schema | tablename | owner --------+-----------+------- test | test_t1 | test test | test_t2 | test test | test_t3 | test (3 rows)
如上为了实现查询效果需要关联三张表，查询比较繁琐，如果使用对象转换就很简单了，如下：
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)
将对象名转为oid类型
转换关系
| 对象类型 | 转换规则 |
|---|---|
| table | '表名'::regclass::oid |
| function/procedure | '函数名/存储过程名'::regproc::oid |
| schema | '模式名'::regnamespace::oid |
| user/role | '用户名/角色名'::regrole::oid |
| type | '类型名称'::regtype::oid |
测试示例
表转换
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)
函数转换
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)模式转换
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)
用户/角色
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)类型
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)到此这篇关于postgresql中将对象oid和对象名相互转换的文章就介绍到这了,更多相关postgresql对象oid和对象名转换内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论