Re: Problem using Subselect results

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