Re: Unions and where optimisation

On Wed, 2003-01-08 at 14:32, Tomasz Myrta wrote:
> Hannu Krosing wrote:
>
> >
> > try making the orderevents view like this:
> >
> > create view orderevents as
> > select rk.aufnr, sub.ts
> >   from rk150 rk,
> >      ( select ts from rk150 where aufnr = rk.aufr
> >        union
> >        select ts from rk151 where aufnr = rk.aufr
> >        union
> >        select ts from rk152 where aufnr = rk.aufr
> >      ) as sub
> > ;
> >
> > this could/should force your desired behavior.
> >
>
> Hannu, does it work?

Nope! Sorry.

SQL spec clearly states that subqueries in FROM clause must not see each
other ;(

It would work in WITH part of the query, which will hopefully be
implemented in some future PG version, perhaps even 7.4 as WITH is the
prerequisite for implementing SQL99 recursive queries, and RedHat has
shown an strongish interest in implementing these.

> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Hannu Krosing <hannu@tm.ee>