Feature Suggestion: Select * from myparent+

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

Browse pgsql-hackers by date

  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