Re: Classes and Inheritance

Hi,

This is something that has been discussed extensively in the hackers
list in recent times (mostly by me :). It is a reasonably extensive
change, affecting front and back ends etc. The syntax suggested has been
something like "select ** from foo" or "select % from foo". The status
of it is I have done the changes to libpq, and am looking into the
backend situation. If you want to help out it would certainly be
welcome.


Thomas Swan wrote:
> 
> I didn't know if this had made it to the list or not... if it has,
> please excuse...
> 
> I've been trying to work on a system of using classes as a variation
> on normalization of data.
> However, I've run into a really frustrating problem.  I had posted the
> following but not in this form to the PGSQL SQL list.
> 
> From that posting...
> --begin--
> 
>      create table foo (id int8);
>      create table bar1 (name text) inherits (foo);
>      create table bar2 (data text) inherits (foo);
>      create table hybrid ( ) inherits (bar1, bar2);
> 
>      INSERT INTO foo VALUES (1);
>      INSERT INTO bar1 VALUES (2,'myname');
>      INSERT INTO bar2 VALUES (3,'mydata');
>      INSERT INTO hybrid VALUES (4,'morename','moredata');
> 
>      I want to do a SELECT * FROM foo*; but I only get the 'id' column
>      as in :
> 
>      id
>      ---
>       1
>       2
>       3
>       4
> 
>      What would be the query to get the following table or a magical
>      way to expand children without knowing the children's table
>      names?
> 
>      I had originally hoped that SELECT * FROM foo* would yield the
>      following, but it's not so.
> 
>      id | name       | data
>      ---+------------+-------------
>       1 | null       | null
>       2 | 'myname'   | null
>       3 | null       | 'mydata'
>       4 | 'morename' | 'moredata'
> 
> --end--
> 
> I can get the same result by doing the following query...
> 
> SELECT * FROM hybrid UNION \
>      (SELECT id,null,data FROM bar2) UNION \
>      (SELECT id,name,null FROM bar1) UNION \
>      (SELECT id,null,null FROM foo)
> 
> However, this doesn't allow for flexibility in the declaration of
> classes as all queries have to be rewritten.
> 
> Proposal
> 
> What I would like to suggest although it may be too much or a little
> odd is to have a way for the column listing to be expanded based on
> the children.   So far, I have been unable to construct a query or
> function that would run through and expand all columns based on
> class/table name and its' children and return a suitable union.
> 
> What if following a class/table by + did this.
> 
> For example the above query could be produced by the following
> statement:
>         SELECT * FROM foo+
> 
> The pattern I noticed it works best starting from the farthest
> child(ren) since the mininum number of columns in a child is >= the
> number of columns in the parent.  However, I also observed this could
> be really awkward depending on the number of columns ancestors had in
> addition to the depth of the tree.    In the example give below, the
> table "hybrid" was unique in that it inherited all from a common set
> of  parents.  It would be different if the farthest branches merged or
> if a branch contained a different data type in the same column
> namespace.
> 
> I did figure a methodology on solving it:
> 
> 1. get the relid of the table/class;
> 2. using pg_inherits, resolve all dependent children.
> 3. create an array of all columns (may be data type conflicts so
> either resolve or error) {you could avoid conflicts with careful
> planning on names}
> 4. union all tables inserting nulls in non-available columns places.
> 
> Note: Step 2 might be easily facilitated if an array of immediate
> children was available from pg_inherits table not just the parent, but
> then inserting classes might be expensive. It's a thought...
> 
> This has been very frustrating even in trying to get information from
> the pg_* system tables.   And, ideally, a user should never have to
> look at those although it is nice to such as in this case.
> 
> I'm afraid this all sounds like a complaint.  Quite to the contrary,
> I've been more than pleased with postgresql and what it has to offer
> and continually look forward to newer releases, new features and
> improvements.
> 
> Thomas
> 
> -
> - Thomas Swan
> - Graduate Student  - Computer Science
> - The University of Mississippi
> -
> - "People can be categorized into two fundamental
> - groups, those that divide people into two groups
> - and those that don't."