PostgreSQL系統表或檢視中pg_node_tree型別值解析
pg_node_tree型別說明
pg_node_tree是一種openGauss/PostgreSQL內部資料型別，用於表示樹形結構的資料。這個資料型別通常對使用者不可見，因此直接查詢或操作它的內容通常需要使用一些PostgreSQL的內建函式或工具。
包含pg_node_tree型別的系統檢視/表，以pg12版本為例：
postgres=# select table_schema,table_name,column_name from information_schema.columns where data_type = 'pg_node_tree';
table_schema | table_name | column_name
--------------+----------------------+----------------
pg_catalog | pg_proc | proargdefaults
pg_catalog | pg_type | typdefaultbin
pg_catalog | pg_class | relpartbound
pg_catalog | pg_attrdef | adbin
pg_catalog | pg_constraint | conbin
pg_catalog | pg_index | indexprs
pg_catalog | pg_index | indpred
pg_catalog | pg_rewrite | ev_qual
pg_catalog | pg_rewrite | ev_action
pg_catalog | pg_trigger | tgqual
pg_catalog | pg_policy | polqual
pg_catalog | pg_policy | polwithcheck
pg_catalog | pg_partitioned_table | partexprs
(13 rows)
postgres=#
解析對應關係
| 系統表/檢視 | 欄位名 | 解析使用表示式 |
|---|---|---|
| pg_proc | proargdefaults | pg_get_expr(proargdefaults,'pg_proc'::regclass) pg_get_function_arguments(oid) |
| pg_type | typdefaultbin | |
| pg_class | relpartbound | pg_get_expr(relpartbound,oid) |
| pg_attrdef | adbin | pg_get_expr(adbin,adrelid) |
| pg_constraint | conbin | pg_get_expr(conbin,conrelid) pg_get_constraintdef(oid) |
| pg_index | indexprs | pg_get_expr(indexprs,indrelid) |
| pg_index | indpred | pg_get_expr(indpred,indrelid) |
| pg_rewrite | ev_qual | |
| pg_rewrite | ev_action | |
| pg_trigger | tgqual | pg_get_triggerdef(oid) |
| pg_policy | polqual | |
| pg_policy | polwithcheck | |
| pg_partitioned_table | partexprs | pg_get_expr(partexprs,partrelid) |
pg_node_tree型別值解析
對於儲存再pg_node_tree型別中的資料，可以使用pg_get_expr()函式將其轉換為可讀的SQL表示式。這個函式將抽象的樹形結構轉換為一個人類可讀的SQL表示式形式。
pg_get_expr函式說明：
pg_get_expr(pg_node_tree, relation_oid)
pg_get_expr(pg_node_tree, relation_oid, pretty_bool)
返回型別：text
反編譯一個表示式的內部形式，假定其中的任何 Var 指向由第二個引數指示的關係
解析pg_attrdef.adbin
pg_attrdef儲存列的預設值。列的主要資訊儲存在pg_attribute。只有那些顯式指定了一個預設值的列才會在這個目錄中有一個項。
-- 建立測試表
drop table if exists test_t;
create table test_t (id int,name varchar(20) default 'test',update_time timestamp default current_timestamp);
-- 原查詢結果
select t1.adrelid::regclass,t2.attname,adbin
from pg_attrdef t1
join pg_attribute t2 on t1.adrelid=t2.attrelid and t1.adnum=t2.attnum
where t2.attnum>0
and t1.adrelid::regclass::text='test_t';
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
adrelid | test_t
attname | name
adbin | {FUNCEXPR :funcid 669 :funcresulttype 1043 :funcretset false :funcvariadic false :funcformat 2 :funccollid 100 :inputcollid 100 :args ({CONST :consttype 1043 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 53 :constvalue 8 [ 32 0 0 0 116 101 115 116 ]} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location -1 :constvalue 4 [ 24 0 0 0 0 0 0 0 ]} {CONST :consttype 16 :consttypmod -1 :constcollid 0 :constlen 1 :constbyval true :constisnull false :location -1 :constvalue 1 [ 0 0 0 0 0 0 0 0 ]}) :location -1}
-[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
adrelid | test_t
attname | update_time
adbin | {FUNCEXPR :funcid 2027 :funcresulttype 1114 :funcretset false :funcvariadic false :funcformat 2 :funccollid 0 :inputcollid 0 :args ({SQLVALUEFUNCTION :op 3 :type 1184 :typmod -1 :location 90}) :location -1}
-- 解析後
select t1.adrelid::regclass,t2.attname,pg_get_expr(adbin,t1.adrelid)
from pg_attrdef t1
join pg_attribute t2 on t1.adrelid=t2.attrelid and t1.adnum=t2.attnum
where t2.attnum>0
and t1.adrelid::regclass::text='test_t';
adrelid | attname | pg_get_expr
---------+-------------+---------------------------
test_t | name | 'test'::character varying
test_t | update_time | CURRENT_TIMESTAMP
(2 rows)
解析pg_proc.proargdefaults
-- 建立測試函式
CREATE OR REPLACE FUNCTION test_fun(
arg1 INTEGER,
arg2 INTEGER DEFAULT 0,
arg3 TEXT DEFAULT 'default_value'
)
RETURNS INTEGER
AS $$
BEGIN
RETURN arg1 + arg2;
END;
$$ LANGUAGE plpgsql;
-- 正常查詢
postgres=# select proargdefaults from pg_proc where proname = 'test_fun';
proargdefaults
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
({CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 80 :constvalue 4 [ 0 0 0 0 0 0 0 0 ]} {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 105 :constvalue 17 [ 68 0 0 0 100 101 102 97 117 108 116 95 118 97 108 117 101 ]})
(1 row)
postgres=#
-- 解析後
postgres=# select pg_get_expr(proargdefaults,'pg_proc'::regclass) from pg_proc where proname = 'test_fun';
pg_get_expr
--------------------------
0, 'default_value'::text
(1 row)
-- 或者
postgres=# select proname,pg_get_function_arguments(oid) from pg_proc where proname = 'test_fun';
proname | pg_get_function_arguments
----------+-------------------------------------------------------------------------------
test_fun | arg1 integer, arg2 integer DEFAULT 0, arg3 text DEFAULT 'default_value'::text
(1 row)
解析pg_index.indexprs和pg_index.indpred
-- 建立測試表和索引
drop table if exists test_t;
create table test_t(id int,name varchar(100));
create index idx_name on test_t(lower(name)) where id >=100000;
-- 查詢
select t3.relname as tablename,
t1.relname as indexname,
t2.indexprs,t2.indpred
from pg_class t1
join pg_index t2 on t1.oid=t2.indexrelid
join pg_class t3 on t3.oid=t2.indrelid
where t3.relname = 'test_t';
-- 原值
tablename | test_t
indexname | idx_name
indexprs | ({FUNCEXPR :funcid 870 :funcresulttype 25 :funcretset false :funcvariadic false :funcformat 0 :funccollid 100 :inputcollid 100 :args ({RELABELTYPE :arg {VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 104 :varcollid 100 :varlevelsup 0 :varnoold 1 :varoattno 2 :location 38} :resulttype 25 :resulttypmod -1 :resultcollid 100 :relabelformat 2 :location -1}) :location 32})
indpred | {OPEXPR :opno 525 :opfuncid 150 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 51} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 56 :constvalue 4 [ -96 -122 1 0 0 0 00 ]}) :location 54}
-- 解析後的值
select t3.relname as tablename,
t1.relname as indexname,
pg_get_expr(t2.indexprs,indrelid),
pg_get_expr(t2.indpred,indrelid)
from pg_class t1
join pg_index t2 on t1.oid=t2.indexrelid
join pg_class t3 on t3.oid=t2.indrelid
where t3.relname = 'test_t';
tablename | indexname | pg_get_expr | pg_get_expr
-----------+-----------+---------------------+----------------
test_t | idx_name | lower((name)::text) | (id >= 100000)
(1 row)
解析pg_class.relpartbound
-- 建立分割槽表
CREATE TABLE test_range_t (id serial,date timestamp(6),data TEXT) PARTITION BY RANGE (date);
CREATE TABLE test_range_2020 PARTITION OF test_range_t FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
CREATE TABLE test_range_2021 PARTITION OF test_range_t FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
CREATE TABLE test_range_2022 PARTITION OF test_range_t FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE test_range_2023 PARTITION OF test_range_t FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
-- 某一個分割槽的值查詢
postgres=# select relname,relpartbound from pg_class where relname = 'test_range_2020';
-[ RECORD 1 ]+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
relname | test_range_2020
relpartbound | {PARTITIONBOUNDSPEC :strategy r :is_default false :modulus 0 :remainder 0 :listdatums <> :lowerdatums ({PARTITIONRANGEDATUM :kind 0 :value {CONST :consttype 1114 :consttypmod 6 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [ 0 96 -62 -122 7 62 2 0 ]} :location 72}) :upperdatums ({PARTITIONRANGEDATUM :kind 0 :value {CONST :consttype 1114 :consttypmod 6 :constcollid 0 :constlen 8 :constbyval true :constisnull false :location -1 :constvalue 8 [0 -96 -83 48 -54 90 2 0 ]} :location 90}) :location 66}
-- 解析後
postgres=# select relname,pg_get_expr(relpartbound,oid) from pg_class where relname ~ 'test_range_\d+';
relname | pg_get_expr
-----------------+--------------------------------------------------------------------
test_range_2020 | FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2021-01-01 00:00:00')
test_range_2021 | FOR VALUES FROM ('2021-01-01 00:00:00') TO ('2022-01-01 00:00:00')
test_range_2022 | FOR VALUES FROM ('2022-01-01 00:00:00') TO ('2023-01-01 00:00:00')
test_range_2023 | FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2024-01-01 00:00:00')
(4 rows)
解析pg_constraint.conbin
drop table if exists test_t;
create table test_t (id int,name varchar(100),check(id>0),check(length(name)>3));
-- 原值查詢
select connamespace::regnamespace as schema,
conrelid::regclass as tablename,
contype,
conname,
conbin
from pg_constraint where conrelid::regclass::text='test_t';
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schema | public
tablename | test_t
contype | c
conname | test_t_id_check
conbin | {OPEXPR :opno 521 :opfuncid 147 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 52} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 55 :constvalue 4 [ 0 0 0 0 0 0 0 0 ]}) :location 54}
-[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schema | public
tablename | test_t
contype | c
conname | test_t_name_check
conbin | {OPEXPR :opno 521 :opfuncid 147 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 1317 :funcresulttype 23 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 100 :args ({RELABELTYPE :arg {VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 104 :varcollid 100 :varlevelsup 0 :varnoold 1 :varoattno 2 :location 71} :resulttype 25 :resulttypmod -1 :resultcollid 100 :relabelformat 2 :location -1}) :location 64} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 77 :constvalue 4 [ 3 0 0 0 0 0 0 0 ]}) :location 76}
-- 解析後
select connamespace::regnamespace as schema,
conrelid::regclass as tablename,
contype,
conname,
pg_get_expr(conbin,conrelid)
from pg_constraint where conrelid::regclass::text='test_t';
schema | tablename | contype | conname | pg_get_expr
--------+-----------+---------+-------------------+----------------------------
public | test_t | c | test_t_id_check | (id > 0)
public | test_t | c | test_t_name_check | (length((name)::text) > 3)
(2 rows)
-- 或者
select connamespace::regnamespace as schema,
conrelid::regclass as tablename,
contype,
conname,
pg_get_constraintdef(oid)
from pg_constraint where conrelid::regclass::text='test_t';
schema | tablename | contype | conname | pg_get_constraintdef
--------+-----------+---------+-------------------+------------------------------------
public | test_t | c | test_t_id_check | CHECK ((id > 0))
public | test_t | c | test_t_name_check | CHECK ((length((name)::text) > 3))
(2 rows)
解析pg_partitioned_table.partexprs
CREATE TABLE sales (
id SERIAL,
sale_date DATE NOT NULL,
amount NUMERIC NOT NULL,
region TEXT NOT NULL
) PARTITION BY RANGE (EXTRACT(YEAR FROM sale_date));
CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES FROM (2022) TO (2023);
CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM (2023) TO (2024);
CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES FROM (2024) TO (2025);
-- 原值
postgres=# select partrelid::regclass,partexprs from pg_partitioned_table where partrelid::regclass::text='sales';
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
partrelid | sales
partexprs | ({FUNCEXPR :funcid 1384 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 100 :args ({CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 149 :constvalue 8 [ 32 0 0 0 121 101 97 114 ]} {VAR :varno 1 :varattno 2 :vartype 1082 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 2 :location 159}) :location 141})
-- 解析後
postgres=# select partrelid::regclass,pg_get_expr(partexprs,partrelid) from pg_partitioned_table where partrelid::regclass::text='sales';
partrelid | pg_get_expr
-----------+------------------------------------
sales | date_part('year'::text, sale_date)
(1 row)
注意：date_part函式等價與extract
postgres=# select extract(year from now()) as extract,date_part('year'::text,now()) as date_part;
extract | date_part
---------+-----------
2024 | 2024
(1 row)
postgres=# select extract(year from current_date) as extract,date_part('year'::text,current_date) as date_part;
extract | date_part
---------+-----------
2024 | 2024
(1 row)
解析pg_trigger.tgqual
-- 建立order表
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
order_date DATE NOT NULL,
amount NUMERIC NOT NULL,
customer_name VARCHAR(100)
);
-- audit_log表
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
order_id INT,
action VARCHAR(50),
log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 建立觸發器函式
CREATE OR REPLACE FUNCTION log_high_value_order()
RETURNS TRIGGER AS $$
BEGIN
-- 檢查訂單金額是否超過 1000
IF NEW.amount > 1000 THEN
-- 插入審計日誌
INSERT INTO audit_log (order_id, action)
VALUES (NEW.id, 'High Value Order');
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 建立帶有條件的觸發器
CREATE TRIGGER high_value_order_trigger
AFTER INSERT ON orders
FOR EACH ROW
WHEN (NEW.amount > 1000)
EXECUTE FUNCTION log_high_value_order();
查詢觸發器資訊
-- 原值
postgres=# select tgrelid::regclass,tgname,tgqual from pg_trigger where tgrelid::regclass::text='orders';
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tgrelid | orders
tgname | high_value_order_trigger
tgqual | {OPEXPR :opno 1756 :opfuncid 1720 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 2 :varattno 3 :vartype 1700 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 2 :varoattno 3 :location 82} {FUNCEXPR :funcid 1740 :funcresulttype 1700 :funcretset false :funcvariadic false :funcformat 2 :funccollid 0 :inputcollid 0 :args ({CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 95 :constvalue 4 [ -24 3 0 0 0 0 0 0 ]}) :location -1}) :location 93}
-- 解析後
postgres=# select tgrelid::regclass,tgname,pg_get_triggerdef(oid) as trigger_definition from pg_trigger where tgrelid::regclass::text='orders';
-[ RECORD 1 ]------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------
tgrelid | orders
tgname | high_value_order_trigger
trigger_definition | CREATE TRIGGER high_value_order_trigger AFTER INSERT ON public.orders FOR EACH ROW WHEN ((new.amount > (1000)::numeric)) EXECUTE FUNCTION log_high_value_order()