PostgreSQL中将对象oid和对象名相互转换
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_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创建测试数据
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::oidfunction/procedure'函数名/存储过程名'::regproc::oidschema'模式名'::regnamespace::oiduser/role'用户名/角色名'::regrole::oidtype'范例名称'::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)
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]