UNION in a VIEW?
От | Gordon A. Runkle |
---|---|
Тема | UNION in a VIEW? |
Дата | |
Msg-id | 9ag0u5$svd$1@news.tht.net обсуждение исходный текст |
Ответы |
Re: UNION in a VIEW?
|
Список | pgsql-sql |
I have a number of views that I'm bringing over from DB2 which have UNIONs in them. Some of the UNIONs have joins. The views are not working as expected (I'm running 7.1RC2). It appears that the where clause being applied to the view by the user is not being distributed properly to the selects. It's probably easiest to give an example: There are two tables, itinerary and conjunctive. A conjunctive is for an itinerary which has > 4 stops (this is imposed by the source data, so we have to preserve the relationships). create table itinerary as ( ticket_nbr, origin, dest, flight_nbr ); create conjunctive as ( ticket_nbr, -- the original ticket number conj_ticket_nbr -- the ticket which extends it ); I've trimmed them a bit for clarity. What the view does is this: create view ticket_conj as ( select ticket_nbr, origin, dest, flight_nbr from itinerary union select c.ticket_nbr i.origin, i.dest, i.flight_nbr from itinerary i inner join conjunctive c on i.ticket_nbr = c.conj_ticket_nbr ); Then we issue queries of this form: select * from ticket_conj where ticket_nbr = '9483'; Sadly, PostgreSQL goes off and munches for a *long* time, whereas DB2 and SQL Server return the desired results promptly. If I write a query like the view, but giving each select a where clause, it works (and faster than the other DBs). It really looks to me (and I am *not* a backend guru) that the where clause is not being bound to the desired value (c.ticket_nbr) in the second select. Does anyone have any ideas on this? Thanks, Gordon. -- It doesn't get any easier, you just go faster. -- Greg LeMond
В списке pgsql-sql по дате отправления: