create view on union -- workaround?

I just discovered that pg does not support views on unions.

Is there a workaround for this?

TIA

--bkh

> -----Original Message-----
> From: owner-pgsql-sql-digest(at)hub(dot)org
> [mailto:owner-pgsql-sql-digest(at)hub(dot)org]
> Sent: Saturday, November 27, 1999 8:01 PM
> To: pgsql-sql-digest(at)hub(dot)org
> Subject: pgsql-sql-digest V1 #426
>
>
>
> pgsql-sql-digest Saturday, November 27 1999 Volume 01 :
> Number 426
>
>
>
> Index:
>
> Usage of index in "ORDER BY" operations
> Re: [SQL] Usage of index in "ORDER BY" operations
> Design Question
> Re: [SQL] Design Question
> Re: [SQL] Design Question
> Re: [SQL] Design Question
> recusrion
>
> ----------------------------------------------------------------------
>
> Date: Sat, 27 Nov 1999 16:32:15 +0100
> From: Matthias Ackermann <matt(at)webcraft(dot)ch>
> Subject: Usage of index in "ORDER BY" operations
>
> I notice following behaviour:
>
> I have a table "adress" with 100'000 adresses
> with columns (last_name, first_name, adressline1, etc.)
> and an index last_name_idx on the column "last_name".
>
> The query
> "SELECT * FROM adress ORDER BY last_name LIMIT 20 OFFSET 0;"
>
> takes forever and "EXPLAIN" shows that the index on last_name
> is not being used.
>
> On the other hand
>
> "SELECT * FROM adress WHERE last_name > '' ORDER BY last_name
> LIMIT 20 OFFSET 0;"
>
> returns the result immediately and "EXPLAIN" shows that the index on
> last_name is being used.
>
> So it seems that inserting a WHERE-clause, even if it doesn't do
> anything at all (i.e. doesn't reduce the result-set),
> is necessary to force the DB to make use of the index.
>
> It even says in the FAQ under 4.9)
> "Indexes are not used for ORDER BY operations."
>
> So I was wondering:
> Am I doing something wrong here or is the lesson simply:
> "Include all attributes of an index in a where-clause
> if you want the indexes to be used"?
>
> Is there a better way to tell the DB to make use of the index?
>
> BTW: This seems to be true for indexes on multiple columns, i.e.
> if having an index on (last_name, first_name) the query had to be:
> SELECT * FROM adress WHERE last_name >'' AND first_name >''
> ORDER BY last_name, first_name LIMIT 20 OFFSET 0;
> Omitting the where-clause again leads to a very slow query.
>
> I apologize if this has been discussed many times before ...
>
> Thanks for your help.
> Matt
>
> ------------------------------
>
> Date: Sat, 27 Nov 1999 12:18:02 -0500
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Subject: Re: [SQL] Usage of index in "ORDER BY" operations
>
> Matthias Ackermann <matt(at)webcraft(dot)ch> writes:
> > So it seems that inserting a WHERE-clause, even if it doesn't do
> > anything at all (i.e. doesn't reduce the result-set),
> > is necessary to force the DB to make use of the index.
>
> This is true in 6.5: it never even considers an indexscan plan unless
> there is a WHERE clause that could make use of the index. 7.0 will
> be smarter. (Current CVS sources already know about making an indexscan
> plan with no other purpose than to satisfy an ORDER BY; in fact they are
> probably *too* eager to make use of an index, and will pick that method
> even when a linear scan and explicit sort would be faster. I need to
> rejigger the cost estimates to be more realistic, especially by taking
> LIMIT into account.)
>
> regards, tom lane
>
> ------------------------------
>
> Date: Sat, 27 Nov 1999 12:25:55 -0600 (CST)
> From: Andy Lewis <alewis(at)roundnoon(dot)com>
> Subject: Design Question
>
> Hello All!
>
> Lets say that I have a DB that I am using to save address info on
> different brokers. Broker name, address, city, state, zip
>
> Some brokers have representation in different states.
>
> How would I be able to save that info so that it can be later selected by
> users looking for a broker in a certain state(s)?
>
> What type of field/table/DB could I save that in for easy reference?
>
> Thanks!
>
> Andy
>
> ------------------------------
>
> Date: Sat, 27 Nov 1999 10:41:42 -0800 (PST)
> From: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
> Subject: Re: [SQL] Design Question
>
> On Sat, 27 Nov 1999, Andy Lewis wrote:
>
> > Lets say that I have a DB that I am using to save address info on
> > different brokers. Broker name, address, city, state, zip
> >
> > Some brokers have representation in different states.
> >
> > How would I be able to save that info so that it can be later
> selected by
> > users looking for a broker in a certain state(s)?
> >
> > What type of field/table/DB could I save that in for easy reference?
>
> Andy,
>
> I suggest that you buy (or borrow from a library) a book on relational
> data base design. To give you the simple answer to your question: have one
> table for the broker's name and identification and a separate table for
> their addresses. It's called a many-to-one structure and is created by
> normalizing the data.
>
> Rich
>
> Dr. Richard B. Shepard, President
>
> Applied Ecosystem Services, Inc. (TM)
> Making environmentally-responsible mining happen.
> (SM)
> --------------------------------
> 2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
> + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) |
> rshepard(at)appl-ecosys(dot)com
>
> ------------------------------
>
> Date: Sat, 27 Nov 1999 12:49:10 -0600 (CST)
> From: Andy Lewis <alewis(at)roundnoon(dot)com>
> Subject: Re: [SQL] Design Question
>
> Thanks, will do....
>
> On Sat, 27 Nov 1999, Rich Shepard wrote:
>
> > On Sat, 27 Nov 1999, Andy Lewis wrote:
> >
> > > Lets say that I have a DB that I am using to save address info on
> > > different brokers. Broker name, address, city, state, zip
> > >
> > > Some brokers have representation in different states.
> > >
> > > How would I be able to save that info so that it can be later
> selected by
> > > users looking for a broker in a certain state(s)?
> > >
> > > What type of field/table/DB could I save that in for easy reference?
> >
> > Andy,
> >
> > I suggest that you buy (or borrow from a library) a book on relational
> > data base design. To give you the simple answer to your
> question: have one
> > table for the broker's name and identification and a separate table for
> > their addresses. It's called a many-to-one structure and is created by
> > normalizing the data.
> >
> > Rich
> >
> > Dr. Richard B. Shepard, President
> >
> > Applied Ecosystem Services, Inc. (TM)
> > Making environmentally-responsible mining happen.
> (SM)
> > --------------------------------
> > 2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
> > + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) |
> rshepard(at)appl-ecosys(dot)com
> >
>
> ------------------------------
>
> Date: Sun, 28 Nov 1999 09:50:33 +1100
> From: Julien Cadiou <julienc(at)vicnet(dot)net(dot)au>
> Subject: Re: [SQL] Design Question
>
> Hi Andy,
>
>
> actually, the postgres site has pretty much what you're after I think.
>
> If you go under documentation, and going through the "tutorial",
> the example they use is quite similar (if I remember correctly)
> to what you're after ...
>
>
> Cheers,
>
>
> At 12:25 27/11/1999 -0600, you wrote:
>
> >Hello All!
>
> >
>
> >Lets say that I have a DB that I am using to save address info on
>
> >different brokers. Broker name, address, city, state, zip
>
> >
>
> >Some brokers have representation in different states.
>
> >
>
> >How would I be able to save that info so that it can be later selected by
>
> >users looking for a broker in a certain state(s)?
>
> >
>
> >What type of field/table/DB could I save that in for easy reference?
>
> >
>
> >Thanks!
>
> >
>
> >Andy
>
> >
>
> >
>
> >************
>
> >
>
> >
>
> >
>
> <bold>Julien CADIOU
>
> </bold>Database Administrator
>
> <bold>VICNET</bold> - Victoria's network
>
> Phone: (03) 9669 9710
>
> Fax: (03) 9669 9805
>
> Web: http://www.vicnet.net.au/
>
> ------------------------------
>
> Date: Sun, 28 Nov 1999 10:00:41 +1100
> From: Julien Cadiou <julienc(at)vicnet(dot)net(dot)au>
> Subject: recusrion
>
> Hi,
>
> I'm doing a portal and have a recursion problem.
> I just need advice on whether or not I'm wasting my time in thinking I can
> do what I need with SQL.
> We're basically building a yahoo-like portal. My categories table is as
> follows this email.
> Each category has a primary key and an owner (the owner is the
> primary key
> of the category owning that category). I want to extract a site map in one
> query: ie: extract the cetagory and its subcategories if any and
> any of the
> subcategories' subcategories if any etc ....
> Right now, in failure to do so with SQL, I'm selecting the whole
> thing in a
> hash array in perl and reorganising it (which is fine, it worksm but if it
> can be "cleaner", that's better !), but I was wondering if I should
> continue looking for the SQL answer ... I've seen similar things done, but
> somehow it's slightly different to this case and I can't seem to see it !
> I've written a few functions that lead me nowhere, performed self
> joins etc
> ... but I think I'm looking at it from the wrong angle ... any
> suggestions ?
> Thanks.
>
> id|owner|name
> - --+-----+-------------------------
> 2| 0|Business
> 9| 7|How to learn
> 12| 9|Good schools
> 13| 12|Good schools in Melbourne
> 14| 13|Good schools in Carlton
> 16| 6|Victorian Private Banks
> 17| 1|Barbeques
> 18| 17|Victorian barbeques
> 3| 1|Sports
> 4| 2|Banking
> 5| 3|Tennis
> 6| 4|Victorian Banks
> 7| 5|Lessons
> 8| 2|Finance
> 1| 0|Outdoors
> 0| 0|Home
> 19| 5|Tennis Clubs
> 20| 19|Melbourne CLubs
> 21| 5|Tournaments
> 22| 13|Free tuition
>
> ------------------------------
>
> End of pgsql-sql-digest V1 #426
> *******************************
>
>
> ************
>