Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: oheinz@stud.fbi.fh-darmstadt.de, pgsql-sql@postgresql.org
Subject: Re: Problem using Subselect results
Date: Mon, 28 Jul 2003 09:47:21 -0700
User-Agent: KMail/1.4.3
References: <1058964708.3f1e84e407a8d@stud.fbi.fh-darmstadt.de>
In-Reply-To: <1058964708.3f1e84e407a8d@stud.fbi.fh-darmstadt.de>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200307280947.21658.josh@agliodbs.com>

Oliver,

> CREATE VIEW my_view AS SELECT b,c
> (SELECT a, b FROM table2 WHERE b=1) my_ab,
> (SELECT  c FROM table3, my_ab WHERE table3.a=my_ab.a) my_c;

This isn't possible in PostgreSQL, and I'm not sure it's possible anywhere.  
HOWEVER, if you put your subselects in the FROM clause instead, like so:

CREATE VIEW my_sub AS
SELECT my_ab.a, my_ab.b, my_c.c
FROM (SELECT a, b FROM table2 WHERE b=1) my_ab,
	(SELECT  a,c FROM table3, my_ab) my_c
WHERE my_ab.a = my_c.a;

OR you can mix-and-match subselect types:

CREATE VIEW my_sub AS
SELECT my_ab.a, my_ab.b,
	(SELECT  c FROM table3, my_ab WHERE table3.a=my_ab.a) my_c
FROM (SELECT a, b FROM table2 WHERE b=1) my_ab;

Although in the simplistic examples above there's not much reason to use a 
subselect at all, of course.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco