create view on union -- workaround? - Mailing list pgsql-sql

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@hub.org
> [mailto:owner-pgsql-sql-digest@hub.org]
> Sent: Saturday, November 27, 1999 8:01 PM
> To: pgsql-sql-digest@hub.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@webcraft.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@sss.pgh.pa.us>
> Subject: Re: [SQL] Usage of index in "ORDER BY" operations
>
> Matthias Ackermann <matt@webcraft.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@roundnoon.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@appl-ecosys.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@appl-ecosys.com
>
> ------------------------------
>
> Date: Sat, 27 Nov 1999 12:49:10 -0600 (CST)
> From: Andy Lewis <alewis@roundnoon.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@appl-ecosys.com
> >
>
> ------------------------------
>
> Date: Sun, 28 Nov 1999 09:50:33 +1100
> From: Julien Cadiou <julienc@vicnet.net.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@vicnet.net.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
> *******************************
>
>
> ************
>