Classes and Inheritance
От | Thomas Swan |
---|---|
Тема | Classes and Inheritance |
Дата | |
Msg-id | 4.3.2.7.2.20000731202714.00b8e1e0@sunset.backbone.olemiss.edu обсуждение исходный текст |
Список | pgsql-hackers |
<i>I didn't know if this had made it to the list or not... if it has, please excuse...<br /><br /></i>I've been trying towork on a system of using classes as a variation on normalization of data.<br /> However, I've run into a really frustratingproblem. I had posted the following but not in this form to the PGSQL SQL list.<br /><br /> From that posting...<br/> --begin-- <dl><dd>create table foo (id int8); <dd>create table bar1 (name text) inherits (foo); <dd>createtable bar2 (data text) inherits (foo); <dd>create table hybrid ( ) inherits (bar1, bar2);<br /><br /><b> </b><dd>INSERTINTO foo VALUES (1); <dd>INSERT INTO bar1 VALUES (2,'myname'); <dd>INSERT INTO bar2 VALUES (3,'mydata'); <dd>INSERTINTO hybrid VALUES (4,'morename','moredata');<br /><br /><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 /><br /><br /><dd>What would be the query to get the following table or amagical way to expand children without knowing the children's table names?<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' </dl>--end--<br /><br /> I can get the same result bydoing the 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>However,this doesn't allow for flexibility in the declaration of classes as all queries have to be rewritten.<br /><br/><b>Proposal<br /><br /></b>What I would like to suggest although it may be too much or a little odd is to have a wayfor the column listing to be expanded based on the children. So far, I have been unable to construct a query or functionthat would run through and expand all columns based on class/table name and its' children and return a suitable union.<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) since the mininum number of columns in a child is >= the number of columns inthe parent. However, I also observed this could be really awkward depending on the number of columns ancestors had inaddition to the depth of the tree. In the example give below, the table "hybrid" was unique in that it inherited allfrom a common set of parents. It would be different if the farthest branches merged or if a branch contained a differentdata type in the same column namespace.<br /><br /> I did figure a methodology on solving it:<br /><br /> 1. getthe relid of the table/class;<br /> 2. using pg_inherits, resolve all dependent children.<br /> 3. create an array ofall columns (may be data type conflicts so either resolve or error) {you could avoid conflicts with careful planning onnames}<br /> 4. union all tables inserting nulls in non-available columns places.<br /><br /> Note: Step 2 might be easilyfacilitated if an array of immediate children was available from pg_inherits table not just the parent, but then insertingclasses might be expensive. It's a thought...<br /><br /> This has been very frustrating even in trying to get informationfrom the pg_* system tables. And, ideally, a user should never have to look at those although it is nice tosuch as in this case.<br /><br /> I'm afraid this all sounds like a complaint. Quite to the contrary, I've been more thanpleased with postgresql and what it has to offer and continually look forward to newer releases, new features and improvements.<br/><br /> Thomas<br /><br /> - <br /> - <b><u>Thomas Swan</u></b> <br />- Graduate Student - Computer Science<br /> - The University of Mississippi<br /> - <br /> - "People can be categorizedinto two fundamental <br /> - groups, those that divide people into two groups <br /> - and those that don't."
В списке pgsql-hackers по дате отправления: