Re: effective SELECT from child tables - Mailing list pgsql-hackers

From Simon Riggs
Subject Re: effective SELECT from child tables
Date
Msg-id 1127931946.19345.217.camel@localhost.localdomain
Whole thread Raw
In response to Re: effective SELECT from child tables  ("Ilia Kantor" <ilia@obnovlenie.ru>)
Responses Re: effective SELECT from child tables
Re: effective SELECT from child tables
List pgsql-hackers
On Wed, 2005-09-28 at 12:13 +0400, Ilia Kantor wrote:
> >> Let table A be inherited by A1, A2, A3.
> >> How to select from A records where actual relations are A1, A2 ?
> 
> >Why not just select directly from the child tables?  I can't get excited
> >about optimizing the case you propose.
> 
> Because "WHERE concrete_class IN (a,b,c)" is much more convenient and
> flexible way of forming select then manually split request into many unions.

> Also, this query runs on top of "abstract class", so inheritance really
> assists me here.
> 

If you treat the sub-class Discriminator as a data item rather than some
additional syntax for class membership then you will find this works
very straightforwardly for you.

Include the Discriminator as a column in A and it will be inherited by
all A1, A2, A3. 
e.g. concrete_class    char(1) not null

then setup constraints on each table like so

ALTER TABLE A1 ADD CHECK (concrete_class = 'A')
ALTER TABLE A2 ADD CHECK (concrete_class = 'B')
ALTER TABLE A3 ADD CHECK (concrete_class = 'C')

then when you run a query like

SELECT * FROM A WHERE concrete_class IN ('A','B')

you will find that table A3, which corresponds to concrete_class C has
been excluded from your query.

Presumably A, B, C are all mutually exclusive, so the end result will be
the same as if you had used a UNION ALL set query.

This will add 1 byte per row in your superclass... and requires no
index. You can even add this as a DEFAULT value for each child table, so
the actual column concrete_class need not be mentioned in an INSERT
statement.

(I've got plans to add an ABSTRACT keyword to tables to follow along the
same set of OO terminology in describing this situation. In next
release, not 8.1)

Best Regards, Simon Riggs




pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: [PATCHES] Proposed patch for sequence-renaming problems
Next
From: Bruce Momjian
Date:
Subject: Added documentation about caching, reliability