如何在PostgreSQL中以编程方式查找继承表？ [英] How to find inherited tables programatically in PostgreSQL?

问题描述

我有一个PostgreSQL 8.3数据库，其中正在使用表继承。我想获得所有表的列表及其模式名称，该名称是使用查询从基表继承的。我们有什么方法可以使用PGSQL来实现这个目标吗？

解决方案

因为你使用的是这样一个旧版本的PostgreSQL你会可能必须使用PL / PgSQL函数来处理> 1的继承深度。在现代PostgreSQL（甚至8.4）上，你将使用递归公用表表达式（ WITH RECURSIVE ）。

pg_catalog.pg_inherits 表是关键。鉴于：

  create table pp（）; - 父我们将搜索
 CREATE TABLE notpp（）; - 多继承的另一个根
 create table cc（）inherits（pp）; -  pp 
的第一级子项创建表dd（）继承（cc，notpp）; -  pp的二级子级，也继承aa 
 create table notshown（）inherits（notpp）; - 仅继承notpp 
的表创建表ccdd（）inherits（cc，dd） - 继承是图形而不是树;加入节点
  

正确的结果将找到 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中的循环来扩展它，使用最后一个循环的子节点作为下一个循环中的父节点。 / p>

更新：这是一个8.3兼容版本，应该递归地查找直接或间接从给定父级继承的所有表。如果使用多重继承，它应该在树的任何位置找到任何将目标表作为其父项之一的表。

  CREATE OR REPLACE FUNCTION find_children（OID）返回$$ 
 SETOF OID SELECT i.inhrelid FROM pg_catalog.pg_inherits哪里i.inhparent = $ 1 
 UNION 
选择find_children（i.inhrelid ）FROM pg_catalog.pg_inherits i WHERE i.inhparent = $ 1; 
 $$ LANGUAGE'sql'稳定; 
 
创建或替换函数find_children_of（parentoid IN regclass，schemaname OUT名称，表名OUT名称）RETURNS SETOF记录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'稳定; 
  

用法：

  regress =＃SELECT * FROM find_children_of（'pp':: regclass）; 
 schemaname | tablename 
 ------------ + ----------- 
 public | cc 
 public | dd 
 public | ccdd 
（3行）
  

这是递归的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 
从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）; 
  

I have a PostgreSQL 8.3 database where table inheritance is being used. I would like to get a list of all tables along with its schema name which is inherited from a base table using query. Is there any way we can get this using PGSQL?

解决方案

Since you're on such an old version of PostgreSQL you'll probably have to use a PL/PgSQL function to handle inheritance depths of > 1. On modern PostgreSQL (or even 8.4) you'd use a recursive common table expression (WITH RECURSIVE).

The pg_catalog.pg_inherits table is the key. Given:

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

A correct result will find cc, dd, and ccdd, but not find notpp or notshown.

A single-depth query is:

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;

... but this will only find cc.

For multi-depth inheritance (ie tableC inherits tableB inherits tableA) you have to extend that via a recursive CTE or a loop in PL/PgSQL, using the children of the last loop as parents in the next.

Update: Here's an 8.3 compatible version that should recursively find all tables that inherit directly or indirectly from a given parent. If multiple inheritance is used, it should find any table that has the target table as one of its parents at any point along the tree.

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;

Usage:

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

Here's the recursive CTE version, which will work if you update Pg, but won't work on your current version. It's much cleaner 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);

这篇关于如何在PostgreSQL中以编程方式查找继承表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！