Geek asks and answers

How to find legacy tables programmatically in PostgreSQL?

I have a PostgreSQL 8.3 database that uses table inheritance. I would like to get a list of all tables along with his schema name, which is inherited from the base table using a query. Is there any way to get this with PGSQL?

+5
source share
4 answers

Since you are using such an old version of PostgreSQL, you probably have to use the PL / PgSQL function to handle inheritance depths> 1. On modern PostgreSQL (or even 8.4), you should use a recursive common expression ( WITH RECURSIVE) table .

The table pg_catalog.pg_inheritsis 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

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

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

8.3, 100%.

+2

, PostgreSQL RECURSIVE , , .

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;
+1

, , ; . :

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_ inherit b.

I suspect that the “best” way to handle this is with a column of text [] and contains (an array of [unsparent :: regclass]) :: text for each table. This will give you something like

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

Despite the fact that it is clearly not ideal, it will at least reveal the full path of inheritance and allow you to access it with sufficient gymnastics. Unfortunately, this is not entirely easy.

A slightly simpler alternative is not to include the full inheritance path at each level, only in each table are direct parents. This will give you the following:

inhrelid    parents
ab_         {a,b}
ba_         {b,a}
ab_ba_      {ab_,ba_}
+1
source

More articles:


All Articles