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

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."
Mon, 31 Jul 2000 20:42:21 -0500
Thomas Swan <tswan@olemiss.edu>
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."

Tue, 01 Aug 2000 14:10:37 +1000
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au>
Re: Classes and Inheritance