From: | Thomas Swan <tswan(at)olemiss(dot)edu> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Feature Suggestion: Select * from myparent+ |
Date: | 2000-07-27 19:57:06 |
Message-ID: | 4.3.2.7.2.20000727125718.020e6848@sunset.backbone.olemiss.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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."
From | Date | Subject | |
---|---|---|---|
Next Message | Kovacs Zoltan Sandor | 2000-07-27 20:00:08 | Re: Industrial-Strength Logging |
Previous Message | Bruce Momjian | 2000-07-27 19:28:06 | Re: New Privilege model purposal |