快捷導(dǎo)航

PostgreSQL中如何將對(duì)象oid和對(duì)象名相互轉(zhuǎn)換

更新時(shí)間：2024年11月11日 08:53:07 作者：kahnyao

文章介紹了在PostgreSQL中如何使用內(nèi)部數(shù)據(jù)類型將對(duì)象OID（對(duì)象標(biāo)識(shí)符）轉(zhuǎn)換為對(duì)象名,從而簡(jiǎn)化系統(tǒng)視圖的關(guān)聯(lián)查詢,文章還提供了不同類型OID與對(duì)象名之間的轉(zhuǎn)換關(guān)系,并通過(guò)示例展示了如何將對(duì)象名轉(zhuǎn)換為OID

PostgreSQL中將對(duì)象oid轉(zhuǎn)為對(duì)象名

使用pg的內(nèi)部數(shù)據(jù)類型將對(duì)象oid轉(zhuǎn)為對(duì)象名，可以簡(jiǎn)化一些系統(tǒng)視圖的關(guān)聯(lián)查詢。

數(shù)據(jù)庫(kù)類型轉(zhuǎn)換對(duì)應(yīng)類型的oid

可以用以下數(shù)據(jù)庫(kù)類型轉(zhuǎn)換對(duì)應(yīng)類型的oid（以pg12為例）

postgres=# select typname from pg_type where typname ~ '^reg';
    typname
---------------
 regclass
 regconfig
 regdictionary
 regnamespace
 regoper
 regoperator
 regproc
 regprocedure
 regrole
 regtype
(10 rows)

對(duì)應(yīng)關(guān)系

對(duì)象名稱	類型	轉(zhuǎn)換規(guī)則
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
	以下幾個(gè)類型暫不確定用途，待研究：
	regprocedure
	regoper
	regconfig

創(chuàng)建測(cè)試數(shù)據(jù)

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);

基于如上測(cè)試數(shù)據(jù)，查詢test模式下有哪些表，以及表的owner

傳統(tǒng)表關(guān)聯(lián)的方式使用以下SQL，關(guān)聯(lián)pg_class、pg_namespace、pg_roles/pg_user

psql -U test -d testdb
-- 查詢用戶關(guān)聯(lián)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)
-- 查詢用戶關(guān)聯(lián)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)

如上為了實(shí)現(xiàn)查詢效果需要關(guān)聯(lián)三張表，查詢比較繁瑣，如果使用對(duì)象轉(zhuǎn)換就很簡(jiǎn)單了，如下：

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)

將對(duì)象名轉(zhuǎn)為oid類型

轉(zhuǎn)換關(guān)系

對(duì)象類型	轉(zhuǎn)換規(guī)則
table	'表名'::regclass::oid
function/procedure	'函數(shù)名/存儲(chǔ)過(guò)程名'::regproc::oid
schema	'模式名'::regnamespace::oid
user/role	'用戶名/角色名'::regrole::oid
type	'類型名稱'::regtype::oid

測(cè)試示例

表轉(zhuǎn)換

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)

函數(shù)轉(zhuǎn)換

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)

模式轉(zhuǎn)換

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)

到此這篇關(guān)于PostgreSQL中將對(duì)象oid和對(duì)象名相互轉(zhuǎn)換的文章就介紹到這了,更多相關(guān)PostgreSQL對(duì)象oid和對(duì)象名轉(zhuǎn)換內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: