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."