Feature Suggestion: Select * from myparent+ - Mailing list pgsql-hackers

I've been trying to work on a system of using classes as a variation on normalization of data.<br /><br /> However,
I'verun into a really frustrating problem.  I had posted this previously but not in this form to the PGSQL SQL list.<br
/><br/> From that posting...<br /> --begin--<br /><dl><dd>create table foo (id int8); <dd>create table bar1 (name text)
inherits(foo); <dd>create table bar2 (data text) inherits (foo); <dd>create table hybrid ( ) inherits (bar1, bar2);<br
/><br/><b> </b><dd>INSERT INTO foo VALUES (1); <dd>INSERT INTO bar1 VALUES (2,'myname'); <dd>INSERT INTO bar2 VALUES
(3,'mydata');<dd>INSERT INTO hybrid VALUES (4,'morename','moredata');<br /><br /><br /><br /><dd>I want to do a
<b>SELECT* FROM foo*</b>; but I only get the 'id' column as in :<br /><br /><font face="Courier, Courier">
</font><dd>id<dd>--- <dd> 1 <dd> 2 <dd> 3 <dd> 4<br /><br /><br /><br /><dd>What would be the query to get the
followingtable or a magical way to expand children?<br /><br /><dd>I had originally hoped that <b>SELECT * FROM
foo*</b>would yield the following, but it's not so.<br /><br /><font face="Courier, Courier"> </font><dd>id |
name      | data <dd>---+------------+------------- <dd> 1 | null       | null <dd> 2 | 'myname'   | null <dd> 3 |
null      | 'mydata' <dd> 4 | 'morename' | 'moredata'<br /><br /></dl>--end--<br /><br /> I can get the same result by
doingthe following query...<br /><br /><b>SELECT * FROM hybrid UNION \<br />      (SELECT id,null,data FROM bar2) UNION
\<br/>      (SELECT id,name,null FROM bar1) UNION \<br />      (SELECT id,null,null FROM foo)<br /><br /></b>What I
wouldlike 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
returnthis.<br /><br /> What if following a class/table by + did this.<br /><br /> For example the above query could be
producedby the following statement:<br /><b>        SELECT * FROM foo+<br /><br /></b>The pattern I noticed it works
beststarting from the farthest child(ren).  However, I also observed this could be really awkward depending on the
numberof 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
samecolumn namespace.<br /><br /> I did figure a methodology on solving it:<br /><br /> 1. get the relid of the
table/class;<br/> 2. using pg_inherits, resolve all dependent children.<br /> 3. create an array of all columns (may be
datatype conflicts so either resolve or error) {you could avoid conflicts with careful planning on names}<br /> 4.
unionall tables inserting nulls in non-available columns places.<br /><br /> Note: Step 2 might be easily facilitated
ifan array of immediate children was available from pg_inherits table not just the parent, but then inserting classes
mightbe expensive. It's a thought...<br /><br /> This has been very frustrating even in trying to get information from
thepg_* system tables.   Ideally a user should never have to look at those.<br /><br /> I'm afraid this all sounds like
acomplaint.  Quite to the contrary, I've been more than pleased with postgresql and what it has to offer and
continuallylook forward to newer releases, new features and improvements.<br /><br /> Thomas<br /><br /> - <br /> -
<b><u>ThomasSwan</u></b>                                   <br /> - Graduate Student  - Computer Science<br /> - The
Universityof Mississippi<br /> - <br /> - "People can be categorized into two fundamental <br /> - groups, those that
dividepeople into two groups <br /> - and those that don't."