做户外运动的网站,招聘网站开发的要求,莆田seo全网营销,免备案网站制作pgsql_postgresql表的继承关系查询 pgsql_postgresql表的继承关系查询前言向上反查表的继承关系SQL系统表说明pg_classpg_namespacepg_inheritspgsql with 语法 pgsql_postgresql表的继承关系查询
前言
表继承是pgsql的一个特性#xff0c;通过表继承可以方便的实现表数据的… pgsql_postgresql表的继承关系查询 pgsql_postgresql表的继承关系查询前言向上反查表的继承关系SQL系统表说明pg_classpg_namespacepg_inheritspgsql with 语法 pgsql_postgresql表的继承关系查询
前言
表继承是pgsql的一个特性通过表继承可以方便的实现表数据的横向拆分表分区从而实现对数据做物理隔离等操作。 继承有别于表分区继承可以很多层级。
比如存储省、市、区的数据时即要物理隔离每层数据又要按层级统一查询此时做个带有继承关系的表是很好的实现方式。 实现方法是建立三个shcema为 province、city、region每个schema中建立一个表 persion 并实现继承关系如下 province.persion - city.persion - region.persion。
管理继承关系是个麻烦事如果库中有1000张表有部分表没有实现三级继承关系如何排查
下面的脚本是一个向上反查父级的SQL通过叶子表向上反查三层既可以得到所有表的继承关系每行是一个表的继承信息。
向上反查表的继承关系SQL
with tab as (--表名schema名select a.oid,a.relname,b.nspname,concat(nspname,.,relname) as tab_full_name from pg_class a,pg_namespace b where a.relnamespaceb.oid
)
--继承关系详情
select(select tab_full_name from tab where oidt.l3_oid limit 1) l3,(select tab_full_name from tab where oidt.l2_oid limit 1) l2,(select tab_full_name from tab where oidt.l1_oid limit 1) l1
from
(--以叶子表反查继承关系select pgt.oid tab_oid,pgt.relname,pgn.nspname,level3.inhrelid as l3_oid,level2.inhrelid as l2_oid,level1.inhrelid as l1_oidfrom pg_class pgt-- 限定schema的普通表叶子级schemajoin pg_namespace pgn on pgt.relnamespace pgn.oid and pgt.relkind r and pgn.nspname my_schema_name-- 查是否存在继承left join pg_inherits level3 on pgt.oid level3.inhrelid-- 查父级left join pg_inherits level2 on level3.inhparent level2.inhrelid-- 查父级的父级left join pg_inherits level1 on level2.inhparent level1.inhparent
) t;系统表说明
pg_class
pgsql也是面向对象的看pg_class就知道该表内记录了pgsql中的所有对象表、视图、主键…)其中查询普通表对象的脚本是:
select * from pg_class where relkindr;pg_namespace
表的命名空间信息即表的schema名字。
--查询表命名空间
select a.oid,a.relname,b.nspname,concat(b.nspname,.,a.relname) fullname
from pg_class a,pg_namespace b
where a.relnamespaceb.oid and b.nspnameassy_def_crp and relkindr ;pg_inherits
表的继承关系比较简单只记录了父表ID。
select * from pg_inherits;pgsql with 语法
WITH提供了一种编写辅助语句的方法以便在较大的查询中使用。这些语句通常被称为公共表表达式或CTE可以认为它们定义了仅为一个查询而存在的临时表。WITH子句中的每个辅助语句可以是SELECT、INSERT、UPDATE或DELETE;WITH子句本身附加到主语句主语句也可以是SELECT、INSERT、UPDATE或DELETE。 这里我们只用到了Select查询。
with tab as (--表名schema名select a.oid,a.relname,b.nspname,concat(nspname,.,relname) as tab_full_name from pg_class a,pg_namespace b where a.relnamespaceb.oid
)可以声明多个临时表之间用逗号分割
withq1 as (select 1),q2 as (select 2),q3 as (select 3)
select * from q1
union all
select * from q2
union all
select * from q3;
