pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gordon A. Runkle <gar@integrated-dynamics.com>
To: pgsql-sql@postgresql.org
Subject: UNION in a VIEW?
Date: Wed, 04 Apr 2001 16:47:59 -0400
Message-ID: <9ag0u5$svd$1@news.tht.net> (raw)

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



view thread (8+ messages)  latest in thread

Message-ID: <9ag0u5$svd$1@news.tht.net>
Permalink:  ../9ag0u5$svd$1@news.tht.net/
Also on:    postgresql.org/message-id/9ag0u5$svd$1@news.tht.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: gar@integrated-dynamics.com
  Subject: Re: UNION in a VIEW?
  In-Reply-To: <9ag0u5$svd$1@news.tht.net>
  References: <9ag0u5$svd$1@news.tht.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox