Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id EE4C4476F5F for ; Wed, 8 Jan 2003 09:55:59 -0500 (EST) Received: from localhost.localdomain (sein.itera.ee [194.126.109.126]) by postgresql.org (Postfix) with ESMTP id DCBAC476F55 for ; Wed, 8 Jan 2003 09:55:58 -0500 (EST) Received: (from hannu@localhost) by localhost.localdomain (8.11.6/8.11.6) id h08GnKU03357; Wed, 8 Jan 2003 16:49:20 GMT X-Authentication-Warning: localhost.localdomain: hannu set sender to hannu@tm.ee using -f Subject: Re: Unions and where optimisation From: Hannu Krosing To: Tomasz Myrta Cc: Boris Klug , "pgsql-performance@postgresql.org" In-Reply-To: <3E1C366B.5080406@klaster.net> References: <200301081425.48597.boris.klug@control.de> <1042041735.3237.1.camel@huli> <3E1C366B.5080406@klaster.net> Content-Type: text/plain Content-Transfer-Encoding: 7bit Organization: Message-Id: <1042044560.3237.7.camel@huli> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 08 Jan 2003 16:49:20 +0000 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/58 X-Sequence-Number: 705 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