From: Josh Berkus <josh@agliodbs.com>
To: oheinz@stud.fbi.fh-darmstadt.de
To: pgsql-sql@postgresql.org
Subject: Re: Problem using Subselect results
Date: Mon, 28 Jul 2003 09:47:21 -0700
Message-ID: <200307280947.21658.josh@agliodbs.com> (raw)
In-Reply-To: <1058964708.3f1e84e407a8d@stud.fbi.fh-darmstadt.de>
References: <1058964708.3f1e84e407a8d@stud.fbi.fh-darmstadt.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, oheinz@stud.fbi.fh-darmstadt.de
Subject: Re: Problem using Subselect results
In-Reply-To: <200307280947.21658.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox