如何在PostgreSQL中编程查找继承表？

Question

我有一个使用表继承的PostgreSQL 8.3数据库。我想通过查询获取所有继承自基础表的表名以及其模式名称列表。是否有方法可以使用PGSQL来实现此功能？

- Arun

Answer 1

由于你使用的是如此老的 PostgreSQL 版本，你可能需要使用 PL/PgSQL 函数来处理继承深度大于 1 的情况。在现代 PostgreSQL 中（甚至是 8.4），你可以使用递归公共表达式 (WITH RECURSIVE)。

pg_catalog.pg_inherits 表是关键。假设有：

create table pp( );     -- The parent we'll search for
CREATE TABLE notpp(); -- Another root for multiple inheritance
create table cc( ) inherits (pp); -- a 1st level child of pp
create table dd( ) inherits (cc,notpp); -- a 2nd level child of pp that also inherits aa
create table notshown( ) inherits (notpp); -- Table that inherits only notpp
create table ccdd () inherits (cc,dd) -- Inheritance is a graph not a tree; join node

正确的结果将会找到 cc, dd, 和 ccdd, 但不会找到 notpp 或 notshown。

一个单层次查询是：

SELECT pg_namespace.nspname, pg_class.relname 
FROM pg_catalog.pg_inherits 
  INNER JOIN pg_catalog.pg_class ON (pg_inherits.inhrelid = pg_class.oid) 
  INNER JOIN pg_catalog.pg_namespace ON (pg_class.relnamespace = pg_namespace.oid) 
WHERE inhparent = 'pp'::regclass;

...但这只能找到cc。

对于多级继承（例如tableC继承自tableB，tableB又继承自tableA），您需要通过递归CTE或在PL/PgSQL中使用循环来扩展该功能，将上一个循环的子项作为下一个循环的父项。

更新：以下是一个与8.3兼容的版本，应该递归地找到从给定父项直接或间接继承的所有表。如果使用了多重继承，则应该找到在树的任何点上具有目标表作为其父项之一的任何表。

CREATE OR REPLACE FUNCTION find_children(oid) RETURNS SETOF oid as $$
SELECT i.inhrelid FROM pg_catalog.pg_inherits i WHERE i.inhparent = $1
UNION
SELECT find_children(i.inhrelid) FROM pg_catalog.pg_inherits i WHERE i.inhparent = $1;
$$ LANGUAGE 'sql' STABLE;

CREATE OR REPLACE FUNCTION find_children_of(parentoid IN regclass, schemaname OUT name, tablename OUT name) RETURNS SETOF record AS $$
SELECT pg_namespace.nspname, pg_class.relname 
        FROM find_children($1) inh(inhrelid) 
          INNER JOIN pg_catalog.pg_class ON (inh.inhrelid = pg_class.oid) 
          INNER JOIN pg_catalog.pg_namespace ON (pg_class.relnamespace = pg_namespace.oid);
$$ LANGUAGE 'sql' STABLE;

使用方法：

regress=# SELECT * FROM find_children_of('pp'::regclass);
 schemaname | tablename 
------------+-----------
 public     | cc
 public     | dd
 public     | ccdd
(3 rows)

这是递归的CTE版本，如果你更新了Pg，它将会起作用，但不会在你当前的版本上起作用。在我看来，这样更加简洁。

WITH RECURSIVE inh AS (
        SELECT i.inhrelid FROM pg_catalog.pg_inherits i WHERE inhparent = 'pp'::regclass
        UNION
        SELECT i.inhrelid FROM inh INNER JOIN pg_catalog.pg_inherits i ON (inh.inhrelid = i.inhparent)
)
SELECT pg_namespace.nspname, pg_class.relname 
    FROM inh 
      INNER JOIN pg_catalog.pg_class ON (inh.inhrelid = pg_class.oid) 
      INNER JOIN pg_catalog.pg_namespace ON (pg_class.relnamespace = pg_namespace.oid);

- Craig Ringer

---

+1 提到了多重继承。我没有想到过。 - user330315

这是一个很棒的查询。我刚刚尝试执行它，结果完美无缺！我修改了一下以适应我们的命名规范。非常感谢你提供这个！ - Arun

Answer 2

以下语句检索public.base_table_name表的所有子表：

select bt.relname as table_name, bns.nspname as table_schema 
from pg_class ct 
    join pg_namespace cns on ct.relnamespace = cns.oid and cns.nspname = 'public' 
    join pg_inherits i on i.inhparent = ct.oid and ct.relname = 'base_table_name' 
    join pg_class bt on i.inhrelid = bt.oid 
    join pg_namespace bns on bt.relnamespace = bns.oid

虽然我不是100%确定，但它应该可以与8.3兼容。

- user330315

---

@CraigRinger，谢谢您的回复。请问如果存在多级继承，是否可以找到子类？ - Arun

Answer 3

对于那些使用支持RECURSIVE的PostgreSQL版本的人，这里有一个函数，可以找到指定基表的派生表。

CREATE OR REPLACE FUNCTION tables_derived_from(base_namespace name, base_table name)
RETURNS TABLE (table_schema name, table_name name, oid oid)
AS $BODY$
    WITH RECURSIVE inherited_id AS
    (
        SELECT i.inhrelid AS oid
        FROM pg_inherits i
        JOIN pg_class base_t ON i.inhparent = base_t.oid
        JOIN pg_namespace base_ns ON base_t.relnamespace = base_ns.oid
        WHERE base_ns.nspname = base_namespace AND base_t.relname = base_table

        UNION

        SELECT i.inhrelid AS oid
        FROM pg_inherits i
        JOIN inherited_id b ON i.inhparent = b.oid
    )
    SELECT child_ns.nspname as table_schema, child_t.relname as table_name, child_t.oid
    FROM inherited_id i
    JOIN pg_class child_t ON i.oid = child_t.oid 
    JOIN pg_namespace child_ns ON child_t.relnamespace = child_ns.oid
    ORDER BY 1, 2, 3;
$BODY$ LANGUAGE sql STABLE;

- EM0

Answer 4

需要注意的是，一个表可以继承多个表，而且上述解决方案都没有充分展示这一点；它们只是遍历单个父级树。请考虑以下情况：

CREATE TABLE a();
CREATE TABLE b();
CREATE TABLE ab_() INHERITS (a,b);
CREATE TABLE ba_() INHERITS (b,a);
CREATE TABLE ab__() INHERITS (ab_);
CREATE TABLE ba__() INHERITS (ba_);
CREATE TABLE ab_ba_() INHERITS (ab_, ba_);
CREATE TABLE ba_ab_() INHERITS (ba_, ab_);

WITH RECURSIVE inh AS (
        SELECT i.inhparent::regclass, i.inhrelid::regclass, i.inhseqno FROM pg_catalog.pg_inherits i WHERE inhparent = 'a'::regclass
        UNION
        SELECT i.inhparent::regclass, i.inhrelid::regclass, i.inhseqno FROM inh INNER JOIN pg_catalog.pg_inherits i ON (inh.inhrelid = i.inhparent)
) SELECT * FROM inh;
 inhparent | inhrelid | inhseqno 
-----------+----------+----------
 a         | ab_      |        1
 a         | ba_      |        2
 ab_       | ab__     |        1
 ba_       | ba__     |        1
 ab_       | ab_ba_   |        1
 ba_       | ab_ba_   |        2
 ba_       | ba_ab_   |        1
 ab_       | ba_ab_   |        2
(8 rows)

请注意，b根本不会出现，这是错误的，因为ab_和ba_都继承了b。

我猜处理这个问题的“最佳”方式是创建一个文本[]列，并包含每个表的（array[inhparent::regclass])::text。这将给您类似以下内容：

inhrelid   path
ab_        {"{a,b}"}
ba_        {"{b,a}"}
ab_ba_     {"{a,b}","{b,a}"}

显然并不理想，但至少可以暴露完整的继承路径，并允许您通过足够的技巧来访问它。不幸的是，构建这个并不容易。

一个稍微简单的替代方法是在每个级别不包括完整的继承路径，只包括每个表的直接父级。这将给您以下内容：

inhrelid    parents
ab_         {a,b}
ba_         {b,a}
ab_ba_      {ab_,ba_}

- Jim Nasby