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

莱莱  论坛元老 | 2024-11-12 20:48:11 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1868|帖子 1868|积分 5604

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要 登录 才可以下载或查看，没有账号？立即注册

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

pg_node_tree类型阐明

pg_node_tree是一种openGauss/PostgreSQL内部数据类型，用于表现树形结构的数据。这个数据类型通常对用户不可见，因此直接查询或操纵它的内容通常必要使用一些PostgreSQL的内置函数或工具。
包含pg_node_tree类型的系统视图/表，以pg12版本为例：
  1. postgres=# select table_schema,table_name,column_name from information_schema.columns where data_type = 'pg_node_tree';
  2. table_schema |      table_name      |  column_name
  3. --------------+----------------------+----------------
  4. pg_catalog   | pg_proc              | proargdefaults
  5. pg_catalog   | pg_type              | typdefaultbin
  6. pg_catalog   | pg_class             | relpartbound
  7. pg_catalog   | pg_attrdef           | adbin
  8. pg_catalog   | pg_constraint        | conbin
  9. pg_catalog   | pg_index             | indexprs
  10. pg_catalog   | pg_index             | indpred
  11. pg_catalog   | pg_rewrite           | ev_qual
  12. pg_catalog   | pg_rewrite           | ev_action
  13. pg_catalog   | pg_trigger           | tgqual
  14. pg_catalog   | pg_policy            | polqual
  15. pg_catalog   | pg_policy            | polwithcheck
  16. pg_catalog   | pg_partitioned_table | partexprs
  17. (13 rows)
  19. postgres=#
复制代码
解析对应关系
系统表/视图字段名解析使用表达式pg_procproargdefaultspg_get_expr(proargdefaults,'pg_proc'::regclass)
pg_get_function_arguments(oid)pg_typetypdefaultbinpg_classrelpartboundpg_get_expr(relpartbound,oid)pg_attrdefadbinpg_get_expr(adbin,adrelid)pg_constraintconbinpg_get_expr(conbin,conrelid)
pg_get_constraintdef(oid)pg_indexindexprspg_get_expr(indexprs,indrelid)pg_indexindpredpg_get_expr(indpred,indrelid)pg_rewriteev_qualpg_rewriteev_actionpg_triggertgqualpg_get_triggerdef(oid)pg_policypolqualpg_policypolwithcheckpg_partitioned_tablepartexprspg_get_expr(partexprs,partrelid)pg_node_tree类型值解析

对于存储再pg_node_tree类型中的数据，可以使用pg_get_expr()函数将其转换为可读的SQL表达式。这个函数将抽象的树形结构转换为一个人类可读的SQL表达式形式。
pg_get_expr函数阐明：
  1. pg_get_expr(pg_node_tree, relation_oid)
  2. pg_get_expr(pg_node_tree, relation_oid, pretty_bool)
  4. 返回类型：text
复制代码
反编译一个表达式的内部形式，假定其中的任何 Var 指向由第二个参数指示的关系
解析pg_attrdef.adbin

pg_attrdef存储列的默认值。列的主要信息存储在pg_attribute。只有那些显式指定了一个默认值的列才会在这个目录中有一个项。
  1. -- 创建测试表
  2. drop table if exists test_t;
  3. create table test_t (id int,name varchar(20) default 'test',update_time timestamp default current_timestamp);
  5. -- 原查询结果
  6. select t1.adrelid::regclass,t2.attname,adbin
  7. from pg_attrdef t1
  8. join pg_attribute t2 on t1.adrelid=t2.attrelid and t1.adnum=t2.attnum
  9. where t2.attnum>0
  10.       and t1.adrelid::regclass::text='test_t';
  11. -[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  12. adrelid | test_t
  13. attname | name
  14. 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}
  15. -[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  16. adrelid | test_t
  17. attname | update_time
  18. 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}
  20. -- 解析后
  21. select t1.adrelid::regclass,t2.attname,pg_get_expr(adbin,t1.adrelid)
  22. from pg_attrdef t1
  23. join pg_attribute t2 on t1.adrelid=t2.attrelid and t1.adnum=t2.attnum
  24. where t2.attnum>0
  25.       and t1.adrelid::regclass::text='test_t';
  26. adrelid |   attname   |        pg_get_expr
  27. ---------+-------------+---------------------------
  28. test_t  | name        | 'test'::character varying
  29. test_t  | update_time | CURRENT_TIMESTAMP
  30. (2 rows)      
复制代码
解析pg_proc.proargdefaults
  1. -- 创建测试函数
  2. CREATE OR REPLACE FUNCTION test_fun(
  3.     arg1 INTEGER,
  4.     arg2 INTEGER DEFAULT 0,
  5.     arg3 TEXT DEFAULT 'default_value'
  6. )
  7. RETURNS INTEGER
  8. AS $$
  9. BEGIN
  10.     RETURN arg1 + arg2;
  11. END;
  12. $$ LANGUAGE plpgsql;
  14. -- 正常查询
  15. postgres=# select proargdefaults from pg_proc where proname = 'test_fun';
  17.                                         proargdefaults
  18. -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  19. ({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 ]})
  20. (1 row)
  22. postgres=#
  26. -- 解析后
  27. postgres=# select pg_get_expr(proargdefaults,'pg_proc'::regclass) from pg_proc where proname = 'test_fun';
  28.        pg_get_expr
  29. --------------------------
  30. 0, 'default_value'::text
  31. (1 row)
  33. -- 或者
  34. postgres=# select proname,pg_get_function_arguments(oid) from pg_proc where proname = 'test_fun';
  35. proname  |                           pg_get_function_arguments
  36. ----------+-------------------------------------------------------------------------------
  37. test_fun | arg1 integer, arg2 integer DEFAULT 0, arg3 text DEFAULT 'default_value'::text
  38. (1 row)
复制代码
解析pg_index.indexprs和pg_index.indpred
  1. -- 创建测试表和索引
  2. drop table if exists test_t;
  3. create table test_t(id int,name varchar(100));
  4. create index idx_name on test_t(lower(name)) where id >=100000;
  6. -- 查询
  7. select t3.relname as tablename,
  8.        t1.relname as indexname,
  9.        t2.indexprs,t2.indpred
  10.    from pg_class t1
  11.   join pg_index t2 on t1.oid=t2.indexrelid
  12.   join pg_class t3 on t3.oid=t2.indrelid
  13. where t3.relname = 'test_t';
  15. -- 原值
  16. tablename | test_t
  17. indexname | idx_name
  18. 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})
  19. 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}
  21. -- 解析后的值
  22. select t3.relname as tablename,
  23.        t1.relname as indexname,
  24.        pg_get_expr(t2.indexprs,indrelid),
  25.        pg_get_expr(t2.indpred,indrelid)
  26.    from pg_class t1
  27.   join pg_index t2 on t1.oid=t2.indexrelid
  28.   join pg_class t3 on t3.oid=t2.indrelid
  29. where t3.relname = 'test_t';
  31. tablename | indexname |     pg_get_expr     |  pg_get_expr
  32. -----------+-----------+---------------------+----------------
  33. test_t    | idx_name  | lower((name)::text) | (id >= 100000)
  34. (1 row)
复制代码
解析pg_class.relpartbound
  1. -- 创建分区表
  2. CREATE TABLE test_range_t (id serial,date timestamp(6),data TEXT) PARTITION BY RANGE (date);
  3. CREATE TABLE test_range_2020 PARTITION OF test_range_t FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
  4. CREATE TABLE test_range_2021 PARTITION OF test_range_t FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
  5. CREATE TABLE test_range_2022 PARTITION OF test_range_t FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
  6. CREATE TABLE test_range_2023 PARTITION OF test_range_t FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
  8. -- 某一个分区的值查询
  9. postgres=# select relname,relpartbound from pg_class where relname = 'test_range_2020';
  10. -[ RECORD 1 ]+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  11. relname      | test_range_2020
  12. 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}
  14. -- 解析后
  15. postgres=# select relname,pg_get_expr(relpartbound,oid) from pg_class where relname ~ 'test_range_\d+';
  16.      relname     |                            pg_get_expr
  17. -----------------+--------------------------------------------------------------------
  18. test_range_2020 | FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2021-01-01 00:00:00')
  19. test_range_2021 | FOR VALUES FROM ('2021-01-01 00:00:00') TO ('2022-01-01 00:00:00')
  20. test_range_2022 | FOR VALUES FROM ('2022-01-01 00:00:00') TO ('2023-01-01 00:00:00')
  21. test_range_2023 | FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2024-01-01 00:00:00')
  22. (4 rows)
复制代码
解析pg_constraint.conbin
  1. drop table if exists test_t;
  2. create table test_t (id int,name varchar(100),check(id>0),check(length(name)>3));
  4. -- 原值查询
  5. select connamespace::regnamespace as schema,
  6.        conrelid::regclass as tablename,
  7.            contype,
  8.            conname,
  9.            conbin
  10. from pg_constraint where conrelid::regclass::text='test_t';
  11. -[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  12. schema    | public
  13. tablename | test_t
  14. contype   | c
  15. conname   | test_t_id_check
  16. 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}
  17. -[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  18. schema    | public
  19. tablename | test_t
  20. contype   | c
  21. conname   | test_t_name_check
  22. 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}
  25. -- 解析后
  26. select connamespace::regnamespace as schema,
  27.        conrelid::regclass as tablename,
  28.            contype,
  29.            conname,
  30.            pg_get_expr(conbin,conrelid)
  31. from pg_constraint where conrelid::regclass::text='test_t';
  33. schema | tablename | contype |      conname      |        pg_get_expr
  34. --------+-----------+---------+-------------------+----------------------------
  35. public | test_t    | c       | test_t_id_check   | (id > 0)
  36. public | test_t    | c       | test_t_name_check | (length((name)::text) > 3)
  37. (2 rows)
  40. -- 或者
  41. select connamespace::regnamespace as schema,
  42.        conrelid::regclass as tablename,
  43.            contype,
  44.            conname,
  45.            pg_get_constraintdef(oid)
  46. from pg_constraint where conrelid::regclass::text='test_t';
  48. schema | tablename | contype |      conname      |        pg_get_constraintdef
  49. --------+-----------+---------+-------------------+------------------------------------
  50. public | test_t    | c       | test_t_id_check   | CHECK ((id > 0))
  51. public | test_t    | c       | test_t_name_check | CHECK ((length((name)::text) > 3))
  52. (2 rows)
复制代码
解析pg_partitioned_table.partexprs
  1. CREATE TABLE sales (
  2.     id SERIAL,
  3.     sale_date DATE NOT NULL,
  4.     amount NUMERIC NOT NULL,
  5.     region TEXT NOT NULL
  6. ) PARTITION BY RANGE (EXTRACT(YEAR FROM sale_date));
  7. CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES FROM (2022) TO (2023);
  8. CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM (2023) TO (2024);
  9. CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES FROM (2024) TO (2025);
  12. -- 原值
  13. postgres=# select partrelid::regclass,partexprs from pg_partitioned_table where partrelid::regclass::text='sales';
  14. -[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  15. partrelid | sales
  16. 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})
  18. -- 解析后
  19. postgres=# select partrelid::regclass,pg_get_expr(partexprs,partrelid) from pg_partitioned_table where partrelid::regclass::text='sales';
  20. partrelid |            pg_get_expr
  21. -----------+------------------------------------
  22. sales     | date_part('year'::text, sale_date)
  23. (1 row)
复制代码
 注意：date_part函数等价与extract 
  1. postgres=# select extract(year from now()) as extract,date_part('year'::text,now()) as date_part;
  2. extract | date_part
  3. ---------+-----------
  4.     2024 |      2024
  5. (1 row)
  7. postgres=# select extract(year from current_date) as extract,date_part('year'::text,current_date) as date_part;
  8. extract | date_part
  9. ---------+-----------
  10.     2024 |      2024
  11. (1 row)
复制代码
解析pg_trigger.tgqual
  1. -- 创建order表
  2. CREATE TABLE orders (
  3.     id SERIAL PRIMARY KEY,
  4.     order_date DATE NOT NULL,
  5.     amount NUMERIC NOT NULL,
  6.     customer_name VARCHAR(100)
  7. );
  9. -- audit_log表
  10. CREATE TABLE audit_log (
  11.     id SERIAL PRIMARY KEY,
  12.     order_id INT,
  13.     action VARCHAR(50),
  14.     log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  15. );
  17. -- 创建触发器函数
  18. CREATE OR REPLACE FUNCTION log_high_value_order()
  19. RETURNS TRIGGER AS $$
  20. BEGIN
  21.     -- 检查订单金额是否超过 1000
  22.     IF NEW.amount > 1000 THEN
  23.         -- 插入审计日志
  24.         INSERT INTO audit_log (order_id, action)
  25.         VALUES (NEW.id, 'High Value Order');
  26.     END IF;
  27.     RETURN NEW;
  28. END;
  29. $$ LANGUAGE plpgsql;
  31. -- 创建带有条件的触发器
  32. CREATE TRIGGER high_value_order_trigger
  33. AFTER INSERT ON orders
  34. FOR EACH ROW
  35. WHEN (NEW.amount > 1000)
  36. EXECUTE FUNCTION log_high_value_order();
复制代码
查询触发器信息
  1. -- 原值
  2. postgres=# select tgrelid::regclass,tgname,tgqual from pg_trigger where tgrelid::regclass::text='orders';
  3. -[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  4. tgrelid | orders
  5. tgname  | high_value_order_trigger
  6. 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}
  9. -- 解析后
  10. postgres=# select tgrelid::regclass,tgname,pg_get_triggerdef(oid) as trigger_definition from pg_trigger where tgrelid::regclass::text='orders';
  11. -[ RECORD 1 ]------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------
  12. tgrelid            | orders
  13. tgname             | high_value_order_trigger
  14. 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()
复制代码
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

莱莱

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

集成商 AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表