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)