Thread

Feature Suggestion: Select * from myparent+

Thomas Swan <tswan@olemiss.edu> — 2000-07-27T19:57:06Z

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 this 
previously 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?

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)

What I would like to suggest although it may be too much or a little odd is 
to have the column listing expanded based on the children.   I have been 
unable to construct a query or function that would run through based on the 
class/table name and return this.

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).  However, I also observed this could be really awkward 
depending on the number of columns ancestors had.    And, 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.   Ideally a user should never have to look at those.

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