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