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
