Thread: determining Inheritance among tables

I have a PostgreSQL database containing a number of tables.  How do I find out which tables are inherited to which?  And once I know that, how do I change(create or revoke) the inheritance?

Thx,
Kevin.

I suppose you would examine pg_class.relhassubclass for true values. You
can then find the oids of child classes in pg_inherits. You're better off
examining the SQL that created the tables in the first place especially if
you are going to modify the tables this way.

As for actually modifying - you've got all that data in your child table
pertaining to inherited attributes. You'll probably do something like
renaming the existing subclass out of the way, create the new class and
then just populate the new table.

Joshua b. Jore ; http://www.greentechnologist.org

On Thu, 4 Jul 2002, Kevin Hyde wrote:

>
> I have a PostgreSQL database containing a number of tables.  How do I find
> out which tables are inherited to which?  And once I know that, how do I
> change(create or revoke) the inheritance?
>
> Thx,
> Kevin.
>

On Thu, 2002-07-04 at 21:14, Kevin Hyde wrote:
>
> I have a PostgreSQL database containing a number of tables.  How do I find
> out which tables are inherited to which?  And once I know that, how do I
> change(create or revoke) the inheritance?


SELECT   a.relname AS child,
         b.relname AS parent
  FROM   pg_class AS a,
         pg_class AS b,
         pg_inherits AS c
  WHERE  a.oid = c.inhrelid AND
         b.oid = c.inhparent;

Thanks.  This makes sense, but I was hoping for a simpler method that I might have overlooked. :-)

After further investigation yesterday, I found that if I created a dump(pg_dump) of the database and then examined the "CREATE TABLE" statements, I could piece together the inheritances among the tables.  And this is kind of what you suggested doing too. 

Inheritance, linking two tables, was a problem in a particular database that I was dealing with.  The way in which the link was finally removed(not by myself though) was to pg_dump the database, manually edit the "CREATE TABLE" statement to build the table without inheritance, and then restore the database.  The person who fixed this didn't explain what exactly they did, and was not available to be asked.   

-----Original Message-----
From: Josh Jore [mailto:josh@greentechnologist.org]
Sent: Friday, July 05, 2002 9:04 AM
To: Kevin Hyde
Cc: 'pgsql-novice@postgresql.org'
Subject: Re: [NOVICE] determining Inheritance among tables

I suppose you would examine pg_class.relhassubclass for true values. You
can then find the oids of child classes in pg_inherits. You're better off
examining the SQL that created the tables in the first place especially if
you are going to modify the tables this way.

As for actually modifying - you've got all that data in your child table
pertaining to inherited attributes. You'll probably do something like
renaming the existing subclass out of the way, create the new class and
then just populate the new table.

Joshua b. Jore ; http://www.greentechnologist.org

On Thu, 4 Jul 2002, Kevin Hyde wrote:

>
> I have a PostgreSQL database containing a number of tables.  How do I find
> out which tables are inherited to which?  And once I know that, how do I
> change(create or revoke) the inheritance?
>
> Thx,
> Kevin.
>