Re: effective SELECT from child tables

On Sat, Oct 01, 2005 at 06:28:03PM +0200, Martijn van Oosterhout wrote:
> Hmm, I'm trying to understand here. If every row in log_8 should have
> the same project_id, couldn't this be acheived by having each row in log_other
> contain the tableoid of the table it refers to. Then a join will return
> the info you're looking for.
>
> Or am I missing something?

log_other will only contain rows where project_id NOT IN (5,8,24,25).
The UNION ALL view is the general purpose way to read data from the
tables. (Actually, since it has rules on it, it's also the way to write
data to the tables as well).

> On Sat, Oct 01, 2005 at 10:57:27AM -0500, Jim C. Nasby wrote:
> > To clarify, this is a hard-coded implementation of what I'm asking for:
> > http://cvs.distributed.net/viewcvs.cgi/stats-sql/logdb/ in a nutshell:
> >
> > CREATE TABLE log_other (
> > project_id smallint NOT NULL
> > ...
> > )
> >
> > CREATE TABLE log_8 (
> > -- No project_id
> > ...
> > )
> > CREATE TABLE log_24, log_25, log_5...
> > CREATE VIEW log AS
> > SELECT * FROM log_other
> > UNION ALL SELECT 8 AS project_id, * FROM log_8
> > ...
> >
> > So the end result is that for cases where project_id is 5, 8, 24, or 25,
> > the data will be stored in tables that don't have the project_id.
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461