pgsql_postgresql表的继承关系查询

  • pgsql_postgresql表的继承关系查询
  • 前言
  • 向上反查表的继承关系SQL
  • 系统表说明
  • pg_class
  • pg_namespace
  • pg_inherits
  • pgsql 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.relnamespace=b.oid
)
--继承关系详情
select
    (select tab_full_name from tab where oid=t.l3_oid limit 1) l3,
    (select tab_full_name from tab where oid=t.l2_oid limit 1) l2,
    (select tab_full_name from tab where oid=t.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_oid
    from pg_class pgt
         -- 限定schema的普通表（叶子级schema）
         join 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 relkind='r';

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.relnamespace=b.oid and b.nspname='assy_def_crp' and relkind='r' ;

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.relnamespace=b.oid
)

可以声明多个临时表，之间用逗号分割：

with
    q1 as (select 1),
    q2 as (select 2),
    q3 as (select 3)
select * from q1
union all
select * from q2
union all
select * from q3;