PostgreSQL系统表或视图中pg_node_tree类型值解析

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
<