postgresql - 如何在 PostgreSQL 中以编程方式查找继承表？

Question

我有一个 PostgreSQL 8.3 数据库，其中使用了表继承。我想获取所有表的列表及其使用查询从基表继承的架构名称。我们有什么办法可以使用 PGSQL 获得它吗？

Answer 1

由于您使用的是这么旧的 PostgreSQL 版本，您可能必须使用 PL/PgSQL 函数来处理大于 1 的继承深度。在现代 PostgreSQL(甚至 8.4)上，您将使用递归公用表表达式(带递归)。

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或未显示。

单深度查询是:

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 继承 tableA)，你必须通过递归 CTE 或循环来扩展它PL/PgSQL，使用上一个循环的 child 作为下一个循环的 parent 。

更新:这是一个 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，它将工作，但不适用于您当前的版本。 IMO 更干净。

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);