Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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 (<code>WITH RECURSIVE</code>).</p> <p>The <code>pg_catalog.pg_inherits</code> table is the key. Given:</p> <pre><code>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 </code></pre> <p>A correct result will find <code>cc</code>, <code>dd</code>, and <code>ccdd</code>, but not find <code>notpp</code> or <code>notshown</code>.</p> <p>A single-depth query is:</p> <pre><code>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; </code></pre> <p>... but this will only find <code>cc</code>.</p> <p>For multi-depth inheritance (ie <code>tableC</code> inherits <code>tableB</code> inherits <code>tableA</code>) 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.</p> <p><strong>Update</strong>: 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.</p> <pre><code>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; </code></pre> <p>Usage:</p> <pre><code>regress=# SELECT * FROM find_children_of('pp'::regclass); schemaname | tablename ------------+----------- public | cc public | dd public | ccdd (3 rows) </code></pre> <p>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.</p> <pre><code>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); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow to find inherited tables programatically in PostgreSQL?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCraig Ringer
    1. USCraig Ringer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to find inherited tables programatically in PostgreSQL?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 for mentioning multiple inheritance. I didn't think of that.
      singulars
      1. PO
      1. USa_horse_with_no_name
    2. COThis is great query. I just tried executing it and it worked perfect!! I modified this to accommodate our naming conventions. Thanks a lot for this...
      singulars
      1. PO
      1. USArun
    3. CO@Arun Glad it helped.
      singulars
      1. PO
      1. USCraig Ringer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload